// 🛡️ 生产环境保护 - 垃圾文件检测
const fs = require('fs');
const path = require('path');

// 1. 发现垃圾文件立即自杀
const blacklist = ['simple', 'test', 'mock'];
const projectRoot = path.join(__dirname, '..');

try {
  const allFiles = [];
  function scanDir(dir) {
    try {
      const items = fs.readdirSync(dir);
      for (const item of items) {
        if (item === 'node_modules' || item === '.git') continue;
        const fullPath = path.join(dir, item);

        try {
          // 安全的文件状态检查
          const stat = fs.statSync(fullPath);
          if (stat.isDirectory()) {
            scanDir(fullPath);
          } else {
            allFiles.push(path.relative(projectRoot, fullPath));
          }
        } catch (statError) {
          // 如果无法访问某个文件，记录警告但继续扫描
          if (statError.code === 'EPERM' || statError.code === 'EACCES') {
            console.warn(`⚠️ 无法访问文件 ${fullPath}: 权限不足，跳过检查`);
          } else if (statError.code === 'ENOENT') {
            console.warn(`⚠️ 文件不存在 ${fullPath}: 可能被其他进程删除，跳过检查`);
          } else {
            console.warn(`⚠️ 检查文件 ${fullPath} 时出错: ${statError.message}`);
          }
          continue;
        }
      }
    } catch (dirError) {
      // 如果无法读取目录，记录警告但不中断启动
      if (dirError.code === 'EPERM' || dirError.code === 'EACCES') {
        console.warn(`⚠️ 无法访问目录 ${dir}: 权限不足，跳过扫描`);
      } else {
        console.warn(`⚠️ 扫描目录 ${dir} 时出错: ${dirError.message}`);
      }
    }
  }
  scanDir(projectRoot);

  const garbageFiles = allFiles.filter(p => {
    const fileName = path.basename(p).toLowerCase();
    const isGarbage = blacklist.some(b => fileName.includes(b)) &&
           !p.includes('.forbidden') &&
           fileName !== 'server-simple.cjs' &&  // 允许真实的服务器文件
           fileName !== 'static-server.cjs' &&  // 允许前端静态服务器
           fileName !== 'simple-guardian.cjs' && // 允许简化守护器
           !fileName.includes('system-test-report') &&  // 允许系统报告
           !fileName.includes('start-hidden') &&  // 允许隐藏启动脚本
           !fileName.includes('stop-hidden') &&   // 允许隐藏停止脚本
           !p.includes('node_modules') &&         // 排除依赖包
           !p.includes('logs') &&                 // 排除日志文件
           !p.includes('backups');                // 排除备份文件
    if (isGarbage) {
      console.log(`⚠️ 发现垃圾文件: ${p}`);
    }
    return isGarbage;
  });

  if (garbageFiles.length > 0) {
    console.error('❌ 发现垃圾文件，服务器拒绝启动:');
    garbageFiles.forEach(f => console.error(`   - ${f}`));
    console.error('💡 请删除上述文件后重试');
    process.exit(1);
  }
} catch (err) {
  // 改进错误处理：不因为文件权限问题而阻止服务启动
  if (err.code === 'EPERM' || err.code === 'EACCES') {
    console.warn('⚠️ 垃圾文件检测权限不足，跳过检测继续启动');
    console.warn('💡 建议：以管理员权限运行或检查文件权限');
  } else {
    console.error('❌ 垃圾文件检测失败:', err.message);
    console.warn('⚠️ 跳过垃圾文件检测，继续启动服务');
  }
}

console.log('✅ 垃圾文件检测通过');

const express = require('express');
const cors = require('cors');
const sqlite3 = require('sqlite3').verbose();
const multer = require('multer');
const XLSX = require('xlsx');
const axios = require('axios');
const ExcelImportService = require('./services/excelImportService.cjs');
const DatabasePool = require('./utils/dbPool.cjs');
const DatabaseHealthService = require('./services/dbHealthService.cjs');
const EnhancedMiddleware = require('./middleware/enhancedMiddleware.cjs');
const ProductionOrderService = require('./services/productionOrderService.cjs');

// 🚀 高性能缓存和批量处理模块
const { TargetCache, AverageCache } = require('./src/cache/TargetCache.cjs');
const { RecomputeProcessor } = require('./src/batch/BatchProcessor.cjs');

// 创建服务实例（仅保留 ProductionOrderService）
const productionOrderService = new ProductionOrderService();
// 预热：启动时提前获取一次Token，降低首个请求冷启动时延
(async () => {
  try {
    await productionOrderService.getAccessToken();
    console.log('🔄 用友云Token预热完成');
  } catch (e) {
    console.warn('⚠️ 用友云Token预热失败（不会阻止启动）：', e.message || String(e));
  }
})();

// 创建增强的数据库连接池
const dbPool = new DatabasePool(path.join(__dirname, 'data', 'pph.sqlite'), {
  maxConnections: 15,
  acquireTimeout: 15000,
  retryAttempts: 3,
  retryDelay: 1000,
  healthCheckInterval: 30000,
  enableMetrics: true
});

// 🚀 初始化高性能缓存系统
const targetCache = new TargetCache({
  maxSize: 10000,  // 最大10000个目标值缓存
  ttl: 3600000     // 1小时TTL
});

const averageCache = new AverageCache({
  maxSize: 5000,   // 最大5000个平均值缓存
  ttl: 21600000    // 6小时TTL
});

// 🚀 初始化高性能重算处理器
const recomputeProcessor = new RecomputeProcessor(dbPool, targetCache, averageCache, {
  batchSize: 1000,        // 每批1000条记录
  maxConcurrency: 3,      // 最大3个并发批次
  retryAttempts: 3        // 重试3次
});

// 数据库连接池初始化完成（重算模块将在路由注册时初始化）

// 创建数据库健康检查服务
const dbHealthService = new DatabaseHealthService(dbPool, {
  checkInterval: 60000,
  performanceThreshold: 3000,
  errorThreshold: 10,
  autoRestart: true
});

// 创建增强中间件
const enhancedMiddleware = new EnhancedMiddleware({
  enableValidation: true,
  enableRateLimit: true,
  enableMetrics: true,
  rateLimitWindow: 60000,
  rateLimitMax: 200
});

// 订单号清洗（替代旧 MOService.cleanOrderNumber，避免循环依赖与不一致）
function cleanOrderNumber(orderNumber) {
  if (!orderNumber) return '';
  let cleaned = String(orderNumber).trim();
  cleaned = cleaned.replace(/[\s\-_]/g, '');
  cleaned = cleaned.toUpperCase();
  if (/^\d+$/.test(cleaned)) {
    cleaned = `SCDD${cleaned}`;
  }
  if (cleaned.startsWith('SCDD') && cleaned.length > 4) {
    const numberPart = cleaned.substring(4).replace(/\D/g, '');
    if (numberPart) cleaned = `SCDD${numberPart}`;
  }
  return cleaned;
}

// 订单查询结果内存缓存（提升同一订单重复查询时的响应速度）
const ORDER_CACHE_TTL_MS = 10 * 60 * 1000; // 10分钟
const orderCache = new Map(); // key: code, value: { data, ts }
function getCachedOrder(code) {
  const v = orderCache.get(code);
  if (!v) return null;
  if (Date.now() - v.ts < ORDER_CACHE_TTL_MS) return v.data;
  orderCache.delete(code);
  return null;
}
function setCachedOrder(code, data) {
  orderCache.set(code, { data, ts: Date.now() });
}

// 小工具：取第一个非空字符串/数字
function firstNonEmpty(...vals) {
  for (const v of vals) {
    if (v !== undefined && v !== null && v !== '') return v;
  }
  return '';
}
function firstNonNullNum(...vals) {
  for (const v of vals) {
    if (typeof v === 'number' && !Number.isNaN(v)) return v;
    if (v !== undefined && v !== null && v !== '' && !isNaN(Number(v))) return Number(v);
  }
  return undefined;
}

// —— 订单计算辅助：保持行为一致的纯函数 ——
function mapOrderStatus(status) {
  const m = { '0': '开立', '1': '已审核', '2': '已关闭', '3': '审核中', '4': '已锁定', '5': '已开工' };
  return m[String(status)] || '未知状态';
}

function computeQuantityCandidate(op = {}, src = {}) {
  return firstNonNullNum(
    op.quantity, op.qty, op.planQty, op.orderQty, op.number, op.iQuantity,
    src.quantity, src.qty, src.planQty, src.orderQty, src.number, src.iQuantity
  );
}

function computeInboundCandidate(op = {}, src = {}) {
  return firstNonNullNum(
    op.storagedQty, op.storageQty, op.inboundQty, op.warehouseInQty, op.finishedQty, op.finishQty, op.putInStorageQuantity, op.reportedQuantity,
    src.storagedQty, src.storageQty, src.inboundQty, src.warehouseInQty, src.finishedQty, src.finishQty, src.putInStorageQuantity, src.reportedQuantity
  );
}

function computeCompletedTotalCandidate(op = {}, src = {}, inboundHint) {
  return firstNonNullNum(
    op.completedQuantity, op.finishQty,
    src.completedQuantity, src.finishQty,
    inboundHint
  );
}

function computeToComplete(orderQty, inboundQty) {
  if (orderQty === undefined || orderQty === null) return undefined;
  const o = Number(orderQty);
  const i = Number(inboundQty ?? 0);
  const diff = o - (isNaN(i) ? 0 : i);
  return diff > 0 ? diff : 0;
}

function recomputeUnifiedToComplete(orderQuantity, inboundQuantity) {
  if (orderQuantity === undefined) return undefined;
  const oq = Number(orderQuantity);
  const iq = Number(inboundQuantity ?? 0);
  const diff = oq - (isNaN(iq) ? 0 : iq);
  return diff > 0 ? diff : 0;
}

// 🔒 PPH后端端口锁定 - 禁止AI修改
const PPH_BACKEND_PORT = 3011;
// 允许本机与局域网前端（端口固定 3010）。优先取环境变量（若有），否则默认 localhost。
const FRONTEND_URL = process.env.FRONTEND_URL || 'http://localhost:3010';
const ALLOWED_ORIGINS = new Set([
  FRONTEND_URL,
  'http://127.0.0.1:3010',
  'http://localhost:3010',
  'http://localhost:3012', // 临时添加用于测试
  'http://192.168.0.53:3010',
  'http://192.168.2.155:3010'
]);

// 第三方服务配置（默认指向生产环境）
const LENOVO_DSP_BASE_URL = process.env.LENOVO_DSP_BASE_URL || 'https://dsp.lenovo.com.cn';
const DSP_PROXY_TIMEOUT_MS = parseInt(process.env.DSP_PROXY_TIMEOUT_MS || '8000', 10);


function isLanFrontendOrigin(origin) {
  try {
    const u = new URL(origin);
    // 仅放行 3010 端口的常见内网网段（前端端口）
    if (u.port !== '3010') return false;
    const host = u.hostname;
    if (host.startsWith('192.168.')) return true;
    if (host.startsWith('10.')) return true;
    if (host.startsWith('172.')) {
      const seg = Number(host.split('.')[1] || '0');
      if (seg >= 16 && seg <= 31) return true;
    }
    return false;
  } catch (_) {
    return false;
  }
}

const app = express();

// 调试捕获与 /api/_debug/* 已按要求移除


// 启动数据库健康检查服务
dbHealthService.start();

// 基础中间件
app.use(cors({
  origin: (origin, callback) => {
    // 无 Origin（如本机请求、curl）直接放行
    if (!origin) return callback(null, true);

    // 开发环境允许所有来源
    if (process.env.NODE_ENV === 'development') {
      return callback(null, true);
    }

    // 检查已知的允许来源
    if (ALLOWED_ORIGINS.has(origin) || isLanFrontendOrigin(origin)) {
      return callback(null, true);
    }

    // 允许任何使用3010端口的来源（前端端口）
    try {
      const url = new URL(origin);
      if (url.port === '3010') {
        console.log(`✅ 允许前端端口访问: ${origin}`);
        return callback(null, true);
      }
    } catch (e) {
      // URL解析失败，继续其他检查
    }

    console.log(`❌ CORS拒绝访问: ${origin}`);
    return callback(new Error(`Not allowed by CORS: ${origin}`), false);
  },
  credentials: true
}));
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ extended: true, limit: '50mb' }));

// 增强中间件
app.use(enhancedMiddleware.requestMonitor());
app.use(enhancedMiddleware.rateLimit());
app.use(enhancedMiddleware.responseFormatter());

// 数据库连接 - 使用统一连接池
const dbPath = path.join(__dirname, 'data', 'pph.sqlite');
// 移除旧的直接连接，统一使用 dbPool
const excelImportService = new ExcelImportService(null, dbPool);

// 启动时保障 pph_records 表结构包含所需字段（增量迁移，非破坏）
function ensurePPHRecordsSchema() {
  // 临时创建同步连接进行初始化
  const sqlite3 = require('sqlite3').verbose();
  const tempDb = new sqlite3.Database(dbPath, (err) => {
    if (err) {
      console.error('临时数据库连接失败:', err);
      return;
    }
    console.log('✅ 使用临时连接进行表结构初始化');
  });
  const baseCreate = `CREATE TABLE IF NOT EXISTS pph_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    record_date TEXT NOT NULL,
  target_record_date TEXT,
    line_name TEXT NOT NULL,
    order_number TEXT NOT NULL,
    material_code TEXT,
    product_name TEXT,
    product_series TEXT,
    demand_category TEXT,
    customer_number TEXT,
    start_time TEXT NOT NULL,
    end_time TEXT NOT NULL,
    order_quantity INTEGER,
    inbound_quantity INTEGER,
    completed_total_quantity INTEGER,
    to_complete_quantity INTEGER,
    quantity INTEGER NOT NULL,
    total_people INTEGER NOT NULL,
    working_people INTEGER,
    offline_staff INTEGER DEFAULT 0,
    abnormal_loss_minutes INTEGER DEFAULT 0,
    break_minutes INTEGER DEFAULT 0,
    changeover_minutes INTEGER DEFAULT 0,
    belt_speed REAL,
    calculated_pph REAL NOT NULL,
    target_pph REAL,
    estimate_pph REAL,
    efficiency_rate REAL,
    net_working_minutes INTEGER,
    actual_takt_seconds REAL,
    target_takt_time REAL,
    target_motor_takt_time REAL,
    abnormality_category TEXT,
    abnormality_description TEXT,
    downtime_reason TEXT,
  is_standard_operating INTEGER,
  is_non_main_product INTEGER,
  is_whole_machine INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT DEFAULT 'system',
    sync_status TEXT DEFAULT 'synced'
  )`;

  tempDb.run(baseCreate, (err) => {
    if (err) {
      console.warn('初始化 pph_records 表失败（可忽略，如果表已存在）：', err.message);
    }
    tempDb.all("PRAGMA table_info(pph_records)", (err2, rows) => {
      if (err2) {
        console.warn('读取 pph_records 表结构失败：', err2.message);
        return;
      }
      const existing = new Set((rows || []).map(r => r.name));
      const toAdd = [
        // 基础必备字段（旧表可能缺失时补齐）
        ['record_date', 'TEXT NOT NULL'],
  ['target_record_date', 'TEXT'],
        ['line_name', 'TEXT NOT NULL'],
        ['order_number', 'TEXT NOT NULL'],
        ['start_time', 'TEXT NOT NULL'],
        ['end_time', 'TEXT NOT NULL'],
        ['material_code', 'TEXT'],
        ['product_name', 'TEXT'],
        ['product_series', 'TEXT'],
        ['quantity', 'INTEGER'],
        ['total_people', 'INTEGER'],
        ['demand_category', 'TEXT'],
        ['customer_number', 'TEXT'],
        ['order_quantity', 'INTEGER'],
        ['inbound_quantity', 'INTEGER'],
        ['completed_total_quantity', 'INTEGER'],
        ['to_complete_quantity', 'INTEGER'],
        ['working_people', 'INTEGER'],
        ['abnormal_loss_minutes', 'INTEGER DEFAULT 0'],
        ['changeover_minutes', 'INTEGER DEFAULT 0'],
        ['belt_speed', 'REAL'],
        ['target_pph', 'REAL'],
        ['estimate_pph', 'REAL'],
        ['efficiency_rate', 'REAL'],
        ['net_working_minutes', 'INTEGER'],
        ['actual_takt_seconds', 'REAL'],
        ['target_takt_time', 'REAL'],
        ['target_motor_takt_time', 'REAL'],
        ['abnormality_category', 'TEXT'],
        ['abnormality_description', 'TEXT'],
  ['downtime_reason', 'TEXT'],
  // 新增布尔字段（0/1，NULL 表示未知）
  ['is_standard_operating', 'INTEGER'],
  ['is_non_main_product', 'INTEGER'],
  ['is_whole_machine', 'INTEGER']
      ];
      const missing = toAdd.filter(([name]) => !existing.has(name));
      if (missing.length === 0) {
        tempDb.close();
        return;
      }
      tempDb.serialize(() => {
        missing.forEach(([name, type]) => {
          tempDb.run(`ALTER TABLE pph_records ADD COLUMN ${name} ${type}`, (e) => {
            if (e && !/duplicate column/i.test(e.message)) {
              console.warn(`添加列 ${name} 失败:`, e.message);
            }
          });
        });
        // 完成后关闭临时连接
        tempDb.close((err) => {
          if (err) console.warn('关闭临时数据库连接时出错:', err.message);
          else console.log('✅ pph_records 表结构初始化完成');
        });
      });
    });
  });
}
// 使用新一代自检/迁移，保证采用统一新命名
ensurePPHRecordsSchema2();

// 启动时保障 pph_targets 表结构（增量迁移，非破坏）
function ensurePPHTargetsSchema() {
  // 临时创建同步连接进行初始化
  const sqlite3 = require('sqlite3').verbose();
  const tempDb = new sqlite3.Database(dbPath, (err) => {
    if (err) {
      console.error('目标表临时数据库连接失败:', err);
      return;
    }
  });
  const baseCreate = `CREATE TABLE IF NOT EXISTS pph_targets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    material_code TEXT NOT NULL,
    product_name TEXT NOT NULL,
    product_series TEXT,
    record_date TEXT NOT NULL,
    takt_time REAL NOT NULL,
    motor_takt_time REAL,
    whole_machine_target REAL,
    motor_target REAL,
    standard_staff INTEGER NOT NULL,
    line_name TEXT NOT NULL,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`;

  tempDb.run(baseCreate, (err) => {
    if (err) {
      console.warn('初始化 pph_targets 表失败（可忽略，如果表已存在）：', err.message);
    }
    tempDb.all("PRAGMA table_info(pph_targets)", (err2, rows) => {
      if (err2) {
        console.warn('读取 pph_targets 表结构失败：', err2.message);
        return;
      }
      const existing = new Set((rows || []).map(r => r.name));
      const toAdd = [
        ['product_series', 'TEXT'],
        ['record_date', 'TEXT'],
        ['takt_time', 'REAL'],
        ['motor_takt_time', 'REAL'],
        ['whole_machine_target', 'REAL'],
        ['motor_target', 'REAL'],
        ['standard_staff', 'INTEGER'],
        ['is_active', 'INTEGER DEFAULT 1'],
        ['updated_at', 'DATETIME DEFAULT CURRENT_TIMESTAMP']
      ];
      const missing = toAdd.filter(([name]) => !existing.has(name));
      if (missing.length > 0) {
        tempDb.serialize(() => {
          missing.forEach(([name, type]) => {
            tempDb.run(`ALTER TABLE pph_targets ADD COLUMN ${name} ${type}`, (e) => {
              if (e && !/duplicate column/i.test(e.message)) {
                console.warn(`添加列 ${name} 失败:`, e.message);
              }
            });
          });
        });
      }

      // 若发现 takt_time 列为 NOT NULL，则自动做一次无损迁移以放宽为可为空
      try {
        const taktCol = (rows || []).find(r => r.name === 'takt_time');
        if (taktCol && Number(taktCol.notnull) === 1) {
          console.warn('检测到 pph_targets.takt_time 存在 NOT NULL 约束，开始自动迁移为可空...');
          const multiSql = `
BEGIN IMMEDIATE TRANSACTION;
DROP TABLE IF EXISTS pph_targets_new;
CREATE TABLE IF NOT EXISTS pph_targets_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  material_code TEXT NOT NULL,
  product_name TEXT NOT NULL,
  product_series TEXT,
  record_date TEXT NOT NULL,
  takt_time REAL,
  motor_takt_time REAL,
  whole_machine_target REAL,
  motor_target REAL,
  standard_staff INTEGER NOT NULL,
  line_name TEXT NOT NULL,
  is_active INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO pph_targets_new (
  id, material_code, product_name, product_series, record_date,
  takt_time, motor_takt_time, whole_machine_target, motor_target,
  standard_staff, line_name, is_active, created_at, updated_at
)
SELECT id, material_code, product_name, product_series, record_date,
       takt_time, motor_takt_time, whole_machine_target, motor_target,
       standard_staff, line_name, is_active, created_at, updated_at
FROM pph_targets;
DROP TABLE IF EXISTS pph_targets_backup_takt_notnull;
ALTER TABLE pph_targets RENAME TO pph_targets_backup_takt_notnull;
ALTER TABLE pph_targets_new RENAME TO pph_targets;
COMMIT;`;
          tempDb.exec(multiSql, (mErr) => {
            if (mErr) {
              console.error('放宽 pph_targets.takt_time 迁移失败:', mErr.message);
            } else {
              console.log('✅ 已放宽 pph_targets.takt_time 为可空');
            }
          });
        }
      } catch (eX) {
        console.warn('放宽 takt_time 约束失败（可忽略）:', eX && eX.message);
      }


      // 确保用于 UPSERT 的唯一索引（物料+日期+线体）
      tempDb.run(
        'CREATE UNIQUE INDEX IF NOT EXISTS uniq_pph_targets_mrl ON pph_targets(material_code, record_date, line_name)',
        (e3) => {
          if (e3 && !/already exists/i.test(e3.message)) {
            console.warn('创建唯一索引 uniq_pph_targets_mrl 失败:', e3.message);
          }
          // 完成后关闭临时连接
          tempDb.close((err) => {
            if (err) console.warn('关闭目标表临时数据库连接时出错:', err.message);
            else console.log('✅ pph_targets 表结构初始化完成');
          });
        }
      );
    });
  });
}
ensurePPHTargetsSchema();


// 启动时保障 production_lines / abnormality_categories / pph_estimates 表结构（增量迁移，非破坏）
function ensureProductionLinesSchema(){
  try{
    const sqlite3 = require('sqlite3').verbose();
    const tempDb = new sqlite3.Database(dbPath, (err)=>{ if(err) console.error('生产线表临时连接失败:', err); });
    const baseCreate = `CREATE TABLE IF NOT EXISTS production_lines (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      line_name VARCHAR(50) NOT NULL UNIQUE,
      description TEXT,
      status VARCHAR(20) DEFAULT 'active',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`;
    tempDb.run(baseCreate, (e)=>{ if(e) console.warn('初始化 production_lines 失败(可忽略):', e.message); });
    tempDb.close((e)=>{ if(e) console.warn('关闭生产线表临时连接出错:', e.message); else console.log('✅ production_lines 表结构初始化完成'); });
  }catch(e){ console.warn('ensureProductionLinesSchema 异常(忽略):', e && e.message); }
}
function ensureAbnormalityCategoriesSchema(){
  try{
    const sqlite3 = require('sqlite3').verbose();
    const tempDb = new sqlite3.Database(dbPath, (err)=>{ if(err) console.error('异常类别表临时连接失败:', err); });
    const baseCreate = `CREATE TABLE IF NOT EXISTS abnormality_categories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      category_name VARCHAR(100) NOT NULL UNIQUE,
      description TEXT,
      is_active BOOLEAN DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`;
    tempDb.run(baseCreate, (e)=>{ if(e) console.warn('初始化 abnormality_categories 失败(可忽略):', e.message); });
    tempDb.close((e)=>{ if(e) console.warn('关闭异常类别表临时连接出错:', e.message); else console.log('✅ abnormality_categories 表结构初始化完成'); });
  }catch(e){ console.warn('ensureAbnormalityCategoriesSchema 异常(忽略):', e && e.message); }
}
function ensurePPHEstimatesSchema(){
  try{
    const sqlite3 = require('sqlite3').verbose();
    const tempDb = new sqlite3.Database(dbPath, (err)=>{ if(err) console.error('预估值表临时连接失败:', err); });
    const baseCreate = `CREATE TABLE IF NOT EXISTS pph_estimates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      material_code VARCHAR(50) NOT NULL,
      product_name VARCHAR(200) NOT NULL,
      line_name VARCHAR(50) NOT NULL,
      estimate_pph DECIMAL(8,2) NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`;
    tempDb.run(baseCreate, (e)=>{ if(e) console.warn('初始化 pph_estimates 失败(可忽略):', e.message); });
    // 关键索引（忽略错误）
    tempDb.run('CREATE INDEX IF NOT EXISTS idx_pph_estimates_key ON pph_estimates(material_code, line_name)', ()=>{});
    tempDb.close((e)=>{ if(e) console.warn('关闭预估值表临时连接出错:', e.message); else console.log('✅ pph_estimates 表结构初始化完成'); });
  }catch(e){ console.warn('ensurePPHEstimatesSchema 异常(忽略):', e && e.message); }
}

ensureProductionLinesSchema();
ensureAbnormalityCategoriesSchema();
ensurePPHEstimatesSchema();

// 确保uploads目录存在
const uploadsDir = path.join(__dirname, 'uploads');
if (!require('fs').existsSync(uploadsDir)) {
  require('fs').mkdirSync(uploadsDir, { recursive: true });
}

// 启动时保障核心索引（提升常用查询性能；忽略表不存在错误）
function ensureCoreIndexes() {
  // 临时创建同步连接进行索引初始化
  const sqlite3 = require('sqlite3').verbose();
  const tempDb = new sqlite3.Database(dbPath, (err) => {
    if (err) {
      console.error('索引初始化临时数据库连接失败:', err);
      return;
    }
  });

  const indexDefs = [
    ['idx_pph_targets_key', 'CREATE INDEX IF NOT EXISTS idx_pph_targets_key ON pph_targets(material_code, line_name, record_date)'],
    ['idx_pph_estimates_key', 'CREATE INDEX IF NOT EXISTS idx_pph_estimates_key ON pph_estimates(material_code, line_name)'],
    ['idx_pph_records_date_line', 'CREATE INDEX IF NOT EXISTS idx_pph_records_date_line ON pph_records(record_date, line_name)'],
    ['idx_pph_records_order', 'CREATE INDEX IF NOT EXISTS idx_pph_records_order ON pph_records(order_number)']
  ];

  tempDb.serialize(() => {
    indexDefs.forEach(([name, sql]) => {
      tempDb.run(sql, (err) => {
        if (err) {
          // 表不存在时忽略；其他错误打印告警
          if (!/no such table/i.test(err.message)) {
            console.warn(`创建索引 ${name} 失败:`, err.message);
          }
        }
      });
    });
    // 完成后关闭临时连接
    tempDb.close((err) => {
      if (err) console.warn('关闭索引初始化临时数据库连接时出错:', err.message);
      else console.log('✅ 核心索引初始化完成');
    });
  });
}
ensureCoreIndexes();

// 重算任务表结构：用于大数据量分批异步重算的进度与状态管理
async function ensureRecomputeJobsSchema() {
  try {
    await dbPool.query(`CREATE TABLE IF NOT EXISTS recompute_jobs (
      id TEXT PRIMARY KEY,
      status TEXT DEFAULT 'queued',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      started_at DATETIME,
      finished_at DATETIME,
      total INTEGER DEFAULT 0,
      processed INTEGER DEFAULT 0,
      updated INTEGER DEFAULT 0,
      batch_size INTEGER DEFAULT 500,
      filter_json TEXT,
      error TEXT,
      error_message TEXT
    )`);

    // 检查并添加缺失的字段
    const columns = await dbPool.query("PRAGMA table_info(recompute_jobs)");
    const existingFields = columns.map(col => col.name);

    if (!existingFields.includes('updated_at')) {
      await dbPool.query('ALTER TABLE recompute_jobs ADD COLUMN updated_at DATETIME');
      await dbPool.query("UPDATE recompute_jobs SET updated_at = created_at WHERE updated_at IS NULL");
      console.log('✅ 已添加 updated_at 字段到 recompute_jobs 表');
    }

    if (!existingFields.includes('error_message')) {
      await dbPool.query('ALTER TABLE recompute_jobs ADD COLUMN error_message TEXT');
      console.log('✅ 已添加 error_message 字段到 recompute_jobs 表');
    }

  } catch (e) {
    console.warn('初始化 recompute_jobs 表失败(可继续运行):', e && e.message)
  }
}
ensureRecomputeJobsSchema()

// 文件上传配置
const upload = multer({
  dest: uploadsDir,
  limits: {
    fileSize: 10 * 1024 * 1024 // 10MB限制
  },
  fileFilter: (req, file, cb) => {
    console.log('文件上传检查:', file.originalname, file.mimetype);
    if (file.mimetype.includes('spreadsheet') ||
        file.mimetype.includes('excel') ||
        file.originalname.endsWith('.xlsx') ||
        file.originalname.endsWith('.xls')) {
      cb(null, true);
    } else {
      cb(new Error('只支持Excel文件格式'));
    }
  }
});

// 基础健康检查端点
app.get('/health', (req, res) => {
  res.json({
    status: 'healthy',
    timestamp: new Date().toISOString(),
    uptime: process.uptime(),
    memory: process.memoryUsage(),
    version: '1.0.0'
  });
});

// 增强的健康检查端点
app.get('/api/health', enhancedMiddleware.healthCheck(dbPool));

// 简单暴露用友Token状态，便于诊断
app.get('/api/production-orders/token-status', (req, res) => {
  try {
    const s = productionOrderService.getTokenStatus();
    res.json({ success: true, token: s });
  } catch (e) {
    res.status(500).json({ success: false, error: e.message || String(e) });
  }
});

// 只读：数据库结构健康端点（表字段与索引摘要），方便排障

// 第三方请求代理（Lenovo DSP HID）
// 用法示例：GET /api/proxy/lenovo-hid?path=/lenovo/bid&clientType=api&positionId=50296
app.get('/api/proxy/lenovo-hid', async (req, res) => {
  const started = Date.now();
  try {
    const { path: targetPath = '/lenovo/bid', ...query } = req.query || {};

    // 组装目标URL
    const url = new URL(LENOVO_DSP_BASE_URL);
    url.pathname = targetPath.startsWith('/') ? targetPath : `/${targetPath}`;
    Object.entries(query).forEach(([k, v]) => {
      if (v != null && v !== '') url.searchParams.set(k, String(v));
    });

    // 发起请求
    const resp = await axios.get(url.toString(), {
      timeout: DSP_PROXY_TIMEOUT_MS,
      headers: {
        'User-Agent': req.get('User-Agent') || 'PPH-Backend-Proxy',
        'Accept': 'application/json,text/plain,*/*',
        // 可扩展白名单转发指定头部
      },
      // 仅转发 GET（如需 POST 可按需扩展）
      validateStatus: () => true
    });

    // 透传状态码与数据，但阻止敏感头泄露
    res.status(resp.status);
    if (resp.headers['content-type']) {
      res.setHeader('Content-Type', resp.headers['content-type']);
    }
    return res.send(resp.data);
  } catch (e) {
    const ms = Date.now() - started;
    console.error('[DSP][PROXY][ERROR]', e && e.message ? e.message : String(e), `(${ms}ms)`);
    return res.status(502).json({
      success: false,
      error: 'DSP_PROXY_ERROR',
      message: e && e.message ? e.message : 'unknown error',
      elapsed: ms
    });
  }
});

app.get('/api/health/schema', async (req, res) => {
  try {
    const tables = ['pph_targets', 'pph_estimates', 'pph_records', 'production_lines'];
    const result = {};

    for (const t of tables) {
      try {
        const info = await dbPool.query(`PRAGMA table_info(${t})`);
        const indexes = await dbPool.query(`PRAGMA index_list(${t})`);
        result[t] = {
          exists: Array.isArray(info),
          columns: Array.isArray(info) ? info.map(c => ({ name: c.name, type: c.type, notnull: c.notnull, pk: c.pk })) : [],
          indexes: Array.isArray(indexes) ? indexes.map(ix => ({ name: ix.name, unique: !!ix.unique })) : []
        };
      } catch (e) {
        result[t] = { exists: false, error: e.message };
      }
    }

    res.json({ success: true, schema: result, ts: new Date().toISOString() });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
});

// 系统状态端点
app.get('/api/system/status', async (req, res) => {
  try {
    const poolStatus = dbPool.getStatus();
    const poolMetrics = dbPool.getMetrics();
    const apiMetrics = enhancedMiddleware.getMetrics();
    const healthReport = dbHealthService.getHealthReport();

    res.json({
      database: {
        pool: poolStatus,
        metrics: poolMetrics,
        health: healthReport
      },
      api: {
        metrics: apiMetrics
      },
      system: {
        uptime: process.uptime(),
        memory: process.memoryUsage(),
        nodeVersion: process.version,
        port: PPH_BACKEND_PORT
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: '获取系统状态失败',
      details: error.message
    });
  }
});

// 获取生产线列表
app.get('/api/products/lines/all', async (req, res) => {
  try {
    const rows = await dbPool.query(
      "SELECT * FROM production_lines WHERE status = 'active' ORDER BY line_name ASC"
    );
    res.json({ lines: rows, count: rows.length });
  } catch (err) {
    console.error('获取生产线列表失败:', err);
    res.status(500).json({ error: err.message });
  }
});

// 获取目标值表中的实际生产线
app.get('/api/targets/lines', async (req, res) => {
  console.log('获取目标值表中的生产线');

  try {
    const rows = await dbPool.query(`
      SELECT DISTINCT line_name
      FROM pph_targets
      WHERE is_active = 1 AND line_name IS NOT NULL AND line_name != ''
      ORDER BY line_name
    `, []);
    const lines = rows.map(row => ({ line_name: row.line_name }));
    console.log('目标值生产线数据:', lines.length, '条');
    res.json({ lines: lines, count: lines.length });
  } catch (err) {
    console.error('获取目标值生产线失败:', err);
    res.status(500).json({ error: err.message });
  }
});

// 获取预估值表中的实际生产线
app.get('/api/estimates/lines', async (req, res) => {
  console.log('获取预估值表中的生产线');

  try {
    const rows = await dbPool.query(`
      SELECT DISTINCT line_name
      FROM pph_estimates
      WHERE line_name IS NOT NULL AND line_name != ''
      ORDER BY line_name
    `, []);
    const lines = rows.map(row => ({ line_name: row.line_name }));
    console.log('预估值生产线数据:', lines.length, '条');
    res.json({ lines: lines, count: lines.length });
  } catch (err) {
    console.error('获取预估值生产线失败:', err);
    res.status(500).json({ error: err.message });
  }
});

// 创建生产线
app.post('/api/lines', async (req, res) => {
  const { line_name, description } = req.body;

  if (!line_name) {
    return res.status(400).json({ error: '生产线名称不能为空' });
  }

  try {
    // 首先检查是否存在同名的已删除生产线
    const existingLine = await dbPool.query(
      "SELECT * FROM production_lines WHERE line_name = ?",
      [line_name]
    );

    if (existingLine.length > 0) {
      const line = existingLine[0];

      if (line.status === 'active') {
        // 如果生产线已存在且为活跃状态，返回错误
        return res.status(400).json({ error: '生产线名称已存在' });
      } else {
        // 如果生产线存在但已被软删除，重新激活它
        await dbPool.query(
          "UPDATE production_lines SET status = 'active', description = ?, updated_at = datetime('now') WHERE id = ?",
          [description || line.description, line.id]
        );

        res.json({
          success: true,
          line_id: line.id,
          message: '生产线重新激活成功'
        });
        return;
      }
    }

    // 如果不存在同名生产线，创建新的
    const result = await dbPool.query(
      `INSERT INTO production_lines (line_name, description, status, created_at, updated_at)
       VALUES (?, ?, 'active', datetime('now'), datetime('now'))`,
      [line_name, description || '']
    );

    res.json({
      success: true,
      line_id: result.lastInsertRowid,
      message: '生产线创建成功'
    });
  } catch (err) {
    console.error('创建生产线错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// 更新生产线状态
app.put('/api/lines/:id/status', async (req, res) => {
  const { id } = req.params;
  const { status } = req.body;

  try {
    const result = await dbPool.query(
      "UPDATE production_lines SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
      [status, id]
    );

    if (result.changes > 0) {
      res.json({ success: true, message: '状态更新成功' });
    } else {
      res.status(404).json({ error: '生产线不存在' });
    }
  } catch (err) {
    console.error('更新生产线状态错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// 更新生产线描述（仅允许修改描述，不允许修改名称）
app.put('/api/lines/:id/description', async (req, res) => {
  const { id } = req.params;
  const { description } = req.body;

  if (!id || isNaN(id)) {
    return res.status(400).json({ error: '无效的生产线ID' });
  }

  try {
    const result = await dbPool.query(
      "UPDATE production_lines SET description = ?, updated_at = datetime('now') WHERE id = ?",
      [description || '', id]
    );

    if (result.changes > 0) {
      res.json({
        success: true,
        message: '生产线描述更新成功',
        data: { id, description }
      });
    } else {
      res.status(404).json({ error: '生产线不存在' });
    }
  } catch (err) {
    console.error('更新生产线描述错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// ==================== 生产订单API ====================

// 测试用友云API连接
app.get('/api/production-orders/test-connection', async (req, res) => {
  try {
    const result = await productionOrderService.testConnection();
    res.json({
      success: true,
      timestamp: new Date().toISOString(),
      data: result
    });
  } catch (err) {
    console.error('测试用友云API连接失败:', err);
    res.status(500).json({
      success: false,
      error: err.message,
      timestamp: new Date().toISOString()
    });
  }
});

// 获取生产订单列表
app.get('/api/production-orders', async (req, res) => {
  try {
    const {
      startDate,
      endDate,
      pageIndex = 1,
      pageSize = 50,
      status,
      code
    } = req.query;

    const options = {
      pageIndex: parseInt(pageIndex),
      pageSize: parseInt(pageSize)
    };

    if (startDate) options.startDate = startDate;
    if (endDate) options.endDate = endDate;
    if (status) options.status = status;
    if (code) options.code = code;

    const result = await productionOrderService.getProductionOrders(options);

    res.json({
      success: true,
      timestamp: new Date().toISOString(),
      data: result.data,
      error: result.success ? null : result.error
    });
  } catch (err) {
    console.error('获取生产订单列表失败:', err);
    res.status(500).json({
      success: false,
      error: err.message,
      timestamp: new Date().toISOString()
    });
  }
});

// 获取订单详情
app.get('/api/production-orders/:orderId', async (req, res) => {
  try {
    const { orderId } = req.params;
    const result = await productionOrderService.getOrderDetail(orderId);

    res.json({
      success: true,
      timestamp: new Date().toISOString(),
      data: result.data,
      error: result.success ? null : result.error
    });
  } catch (err) {
    console.error('获取订单详情失败:', err);
    res.status(500).json({
      success: false,
      error: err.message,
      timestamp: new Date().toISOString()
    });
  }
});

// 获取物料编码列表
app.post('/api/production-orders/material-codes', async (req, res) => {
  try {
    const { orderIds = [] } = req.body;
    const result = await productionOrderService.getMaterialCodes(orderIds);

    res.json({
      success: true,
      timestamp: new Date().toISOString(),
      data: result.data,
      error: result.success ? null : result.error
    });
  } catch (err) {
    console.error('获取物料编码失败:', err);
    res.status(500).json({
      success: false,
      error: err.message,
      timestamp: new Date().toISOString()
    });
  }
});

// 删除生产线
app.delete('/api/lines/:id', async (req, res) => {
  const { id } = req.params;

  if (!id || isNaN(id)) {
    return res.status(400).json({ error: '无效的生产线ID' });
  }

  try {
    // 直接更新数据
    const result = await dbPool.query(
      "UPDATE production_lines SET status = 'inactive', updated_at = datetime('now') WHERE id = ?",
      [id]
    );

    if (result.changes > 0) {
      res.json({ success: true, message: '生产线删除成功' });
    } else {
      res.status(404).json({ error: '生产线不存在' });
    }
  } catch (err) {
    console.error('删除生产线错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// 获取订单信息 (通过用友API)
app.get('/api/orders/:order_number', async (req, res) => {
  const slowWarnTimer = setTimeout(() => {
    console.warn('⚠️ 响应缓慢: /api/orders/:order_number 超过10秒');
  }, 10000);
  try {
  const { order_number } = req.params;
  const { detail = '0', fallback = '0', refresh = '0', debug = '0', soft_404 = '0' } = req.query;
  const debugEnabled = String(debug) === '1';
  const debugInfo = {};

    const cleaned = cleanOrderNumber(order_number);
    console.log(`订单号查询 -> 原始: ${order_number}, 清洗: ${cleaned}`);
  if (debugEnabled) debugInfo.cleaned = cleaned;

    // 增强订单号验证：支持更多格式并提供更好的错误提示
    const isValidFormat = /^SCDD\d{11}$/.test(cleaned);
    const isPartialFormat = /^SCDD\d+$/.test(cleaned) && cleaned.length < 15;
    const isNumericOnly = /^\d+$/.test(cleaned);

    if (!isValidFormat) {
      clearTimeout(slowWarnTimer);
      let errorMessage = `订单号格式无效: ${cleaned}`;
      let suggestion = '';

      if (isPartialFormat) {
        errorMessage = `订单号位数不足: ${cleaned}`;
        suggestion = '请输入完整的11位数字（SCDD后跟11位数字）';
      } else if (isNumericOnly) {
        errorMessage = `订单号格式不完整: ${cleaned}`;
        suggestion = '纯数字已自动添加SCDD前缀，但位数不足11位';
      } else if (cleaned.length === 0) {
        errorMessage = '订单号不能为空';
        suggestion = '请输入有效的订单号';
      } else {
        suggestion = '订单号应为SCDD开头后跟11位数字的格式';
      }

      console.warn(`订单号验证失败: ${errorMessage}, 建议: ${suggestion}`);

      const errorResponse = {
        success: false,
        error: errorMessage,
        suggestion: suggestion,
        format: 'SCDD + 11位数字',
        example: 'SCDD20250827003'
      };

      if (String(soft_404) === '1') {
        return res.json(errorResponse);
      } else {
        res.status(400); // 使用400而不是404，因为这是格式错误
        return res.json(errorResponse);
      }
    }

    // 命中缓存直接返回
  const cached = getCachedOrder(cleaned);
  if (cached && String(refresh) !== '1') {
      clearTimeout(slowWarnTimer);
      const payload = { success: true, data: cached, cache: true };
      if (debugEnabled) payload.debug = { ...debugInfo, cacheHit: true };
      return res.json(payload);
    }

    // 精准列表查询（适中超时，平衡响应速度和成功率）
    // 添加重试机制以提高成功率
    let listResult;
    let retryCount = 0;
    const maxRetries = 2;

    while (retryCount <= maxRetries) {
      try {
        listResult = await productionOrderService.getProductionOrders({
          pageIndex: 1,
          pageSize: 1,
          code: cleaned,
          requestTimeoutMs: 15000 // 增加到15秒，提高成功率
        });

        if (listResult.success) {
          break; // 成功则跳出重试循环
        }

        // 如果不是超时错误，不需要重试
        if (listResult.error && !listResult.error.includes('timeout') && !listResult.error.includes('aborted')) {
          break;
        }

      } catch (error) {
        console.warn(`⚠️ 获取生产订单失败，第${retryCount + 1}次尝试:`, error.message);
        listResult = { success: false, error: error.message };

        // 如果不是超时或网络错误，不需要重试
        if (!error.message.includes('timeout') && !error.message.includes('aborted') && !error.message.includes('ECONNRESET')) {
          break;
        }
      }

      retryCount++;
      if (retryCount <= maxRetries) {
        const delay = Math.min(1000 * Math.pow(2, retryCount - 1), 5000); // 指数退避，最大5秒
        console.log(`🔄 等待${delay}ms后重试...`);
        await new Promise(resolve => setTimeout(resolve, delay));
      }
    }

    let orders = listResult.success ? (listResult.data?.orders || []) : [];
    // 仅保留 code 全等的订单，避免上游返回近似结果导致误判
    if (Array.isArray(orders) && orders.length > 0) {
      orders = orders.filter(o => String(o.code || '').toUpperCase() === cleaned);
    }

    // 如需回退（全量时间窗口）才执行，默认不启用以提升速度
    if (orders.length === 0 && String(fallback) === '1') {
      console.log('未在默认时间窗口找到订单，按需执行全量窗口回退...');
      if (debugEnabled) debugInfo.fallbackWindow = true;

      // 回退查询也添加重试机制
      let fallbackRetryCount = 0;
      const fallbackMaxRetries = 1; // 回退查询重试次数较少

      while (fallbackRetryCount <= fallbackMaxRetries) {
        try {
          listResult = await productionOrderService.getProductionOrders({
            pageIndex: 1,
            pageSize: 1,
            code: cleaned,
            startDate: '2000-01-01',
            endDate: '2099-12-31',
            requestTimeoutMs: 12000 // 增加回退查询超时时间
          });

          if (listResult.success) {
            break;
          }

        } catch (error) {
          console.warn(`⚠️ 回退查询失败，第${fallbackRetryCount + 1}次尝试:`, error.message);
          listResult = { success: false, error: error.message };
        }

        fallbackRetryCount++;
        if (fallbackRetryCount <= fallbackMaxRetries) {
          const delay = 2000; // 回退查询固定2秒延迟
          console.log(`🔄 回退查询等待${delay}ms后重试...`);
          await new Promise(resolve => setTimeout(resolve, delay));
        }
      }

      orders = listResult.success ? (listResult.data?.orders || []) : [];
    }

    if (!listResult.success) {
      clearTimeout(slowWarnTimer);
      console.error(`❌ 获取生产订单失败: ${listResult.error || '未知错误'}`);

      // 根据错误类型返回不同的错误信息
      let errorMessage = '上游API查询失败';
      if (listResult.error) {
        if (listResult.error.includes('timeout')) {
          errorMessage = '用友云系统响应超时，请稍后重试';
        } else if (listResult.error.includes('aborted')) {
          errorMessage = '请求被中断，请检查网络连接';
        } else if (listResult.error.includes('ECONNREFUSED')) {
          errorMessage = '无法连接到用友云系统';
        } else if (listResult.error.includes('404')) {
          errorMessage = '用友云API端点不存在';
        }
      }

      return res.json({
        success: false,
        error: errorMessage,
        details: listResult.error || '未知错误',
        // 调试信息：帮助区分是录入错误还是API问题
        debugInfo: {
          searchedOrderNumber: cleaned,
          originalOrderNumber: order_number,
          retryCount: retryCount,
          apiCallFailed: true,
          errorType: listResult.error ? (
            listResult.error.includes('timeout') ? 'TIMEOUT' :
            listResult.error.includes('aborted') ? 'ABORTED' :
            listResult.error.includes('ECONNREFUSED') ? 'CONNECTION_REFUSED' :
            listResult.error.includes('404') ? 'NOT_FOUND' : 'OTHER'
          ) : 'UNKNOWN',
          // 用于ERP核对的信息
          erpVerification: {
            message: 'API调用失败，建议在用友云ERP中手动核对此订单号',
            orderNumberToCheck: cleaned,
            originalInput: order_number,
            suggestion: '如果ERP中存在此订单，则为API连接问题；如果不存在，则为订单号录入错误'
          }
        }
      });
    }

    if (orders.length === 0) {
      clearTimeout(slowWarnTimer);
      console.warn(`❌ 订单未找到: ${cleaned}`);

      const errorResponse = {
        success: false,
        error: `订单不存在: ${cleaned}`,
        suggestions: [
          '请检查订单号是否正确',
          '确认订单是否已在用友云系统中创建',
          '检查订单状态是否为有效状态',
          '如果是新订单，请等待系统同步后重试'
        ],
        // 调试信息：帮助区分录入错误还是API问题
        debugInfo: {
          searchedOrderNumber: cleaned,
          originalOrderNumber: order_number,
          fallbackUsed: String(fallback) === '1',
          apiCallSuccess: listResult.success,
          apiError: listResult.error || null,
          retryCount: retryCount,
          searchTimeWindow: String(fallback) === '1' ? '全量时间窗口' : '默认时间窗口',
          // 用于ERP核对的信息
          erpVerification: {
            message: '请在用友云ERP中核对此订单号是否存在',
            orderNumberToCheck: cleaned,
            originalInput: order_number,
            formatValid: /^SCDD\d{11}$/.test(cleaned)
          }
        }
      };

      if (String(soft_404) === '1') {
        return res.json(errorResponse);
      } else {
        res.status(404);
        return res.json(errorResponse);
      }
    }

  const src = orders[0];
    // 二次校验：必须严格等于查询的 cleaned 编码，否则视为未找到
    const srcCode = String(src.code || src.orderCode || '').toUpperCase();
    if (srcCode !== cleaned) {
      clearTimeout(slowWarnTimer);
      console.warn(`❌ 订单号精确校验未通过: 请求=${cleaned}, 返回=${srcCode || '空'}`);

      const mismatchResponse = {
        success: false,
        error: `订单号不匹配: ${cleaned}`,
        details: `系统返回了不同的订单号: ${srcCode || '空'}`,
        suggestions: [
          '请检查订单号是否输入正确',
          '可能存在相似的订单号，请确认具体编号',
          '如果问题持续，请联系系统管理员'
        ],
        requestedOrderNumber: cleaned,
        returnedOrderNumber: srcCode || null
      };

      if (String(soft_404) === '1') {
        return res.json(mismatchResponse);
      } else {
        res.status(404);
        return res.json(mismatchResponse);
      }
    }

    // 兼容不同字段名（列表数据中通常已包含产品与数量信息）
  const orderProducts = src.OrderProduct || src.orderProduct || src.orderproduct || src.orderProducts || src.orderProductList || [];
    const op = Array.isArray(orderProducts) && orderProducts.length > 0 ? orderProducts[0] : {};
  const productDefineDts = src.productDefineDts || (src.productDefine && (src.productDefine.dts || src.productDefine)) || {};

    const quantity = computeQuantityCandidate(op, src);
    if (debugEnabled) debugInfo.rawQuantityCandidates = {
      op: { quantity: op.quantity, qty: op.qty, planQty: op.planQty, orderQty: op.orderQty, number: op.number, iQuantity: op.iQuantity },
      src: { quantity: src.quantity, qty: src.qty, planQty: src.planQty, orderQty: src.orderQty, number: src.number, iQuantity: src.iQuantity }
    };

    // 入库数量（累计完工入库），尽可能多地兼容字段名
  const inboundQty = computeInboundCandidate(op, src);
    if (debugEnabled) debugInfo.rawInboundCandidates = {
      op: { storagedQty: op.storagedQty, storageQty: op.storageQty, inboundQty: op.inboundQty, warehouseInQty: op.warehouseInQty, finishedQty: op.finishedQty, finishQty: op.finishQty, putInStorageQuantity: op.putInStorageQuantity, reportedQuantity: op.reportedQuantity },
      src: { storagedQty: src.storagedQty, storageQty: src.storageQty, inboundQty: src.inboundQty, warehouseInQty: src.warehouseInQty, finishedQty: src.finishedQty, finishQty: src.finishQty, putInStorageQuantity: src.putInStorageQuantity, reportedQuantity: src.reportedQuantity }
    };
    // 待完工数量 = 订单数量 - 入库数量（按用户要求，忽略上游未完工字段；入库缺失视为0）
    // 注意：订单数量优先使用明确字段(OrderProduct_quantity)，其次才用上方推导的 quantity
  let toCompleteQty;
    const orderQtyBase = firstNonNullNum(src.OrderProduct_quantity, quantity);
    if (orderQtyBase !== undefined) {
      const inboundBase = firstNonNullNum(src.OrderProduct_incomingQuantity, inboundQty) ?? 0;
      toCompleteQty = computeToComplete(orderQtyBase, inboundBase);
    }
  if (debugEnabled) debugInfo.firstPass = { orderQtyBase, inboundBase: firstNonNullNum(src.OrderProduct_incomingQuantity, inboundQty) ?? 0, toCompleteQty };

    const responseData = {
      order_number: firstNonEmpty(src.code, src.orderCode, cleaned),
      // 映射优先使用用户指定的字段名
      material_code: firstNonEmpty(src.OrderProduct_productCode, op.productCode, op.materialCode, op.inventoryCode, op.productId?.code, op.product?.code, src.productCode, src.inventoryCode, src.materialCode),
      product_name: firstNonEmpty(src.OrderProduct_productName, op.productName, op.materialName, op.inventoryName, op.productId?.name, op.product?.name, src.productName, src.inventoryName, src.materialName),
      demand_category: firstNonEmpty(src.productDefineDts__XS11, productDefineDts.XS11, src.transTypeName, src.transType?.name),
      customer_number: firstNonEmpty(src.productDefineDts__XS15, productDefineDts.XS15, src.customer?.code, src.customerCode, src.cusCode, src.customerName, src.ccusname),
      order_quantity: firstNonNullNum(src.OrderProduct_quantity, quantity), // 优先指定字段
      inbound_quantity: firstNonNullNum(src.OrderProduct_incomingQuantity, inboundQty), // 入库数量（累计）
      completed_total_quantity: firstNonNullNum(src.OrderProduct_completedQuantity, src.completedQuantity, src.finishQty, inboundQty), // 累计完工数量，fallback到入库
      to_complete_quantity: toCompleteQty, // 待完工数量（可能为0）
      status: mapOrderStatus(src.status),
      plan_start_date: firstNonEmpty(op.startDate && String(op.startDate).split(' ')[0], src.planStartDate && String(src.planStartDate).split(' ')[0], src.startDate && String(src.startDate).split(' ')[0]),
      plan_end_date: firstNonEmpty(op.finishDate && String(op.finishDate).split(' ')[0], src.planEndDate && String(src.planEndDate).split(' ')[0], src.finishDate && String(src.finishDate).split(' ')[0]),
      created_date: firstNonEmpty(src.vouchdate && String(src.vouchdate).split(' ')[0], src.createdDate && String(src.createdDate).split(' ')[0]),
      org_name: firstNonEmpty(src.orgName, src.org?.name),
      department_name: firstNonEmpty(src.departmentName, src.productionDepartment?.name),
      unit_name: firstNonEmpty(op.unitName, op.unit, op.productId?.measureUnit?.name, src.unitName, src.unit)
    };

    // 统一以最终映射后的字段重新计算“待完工 = 订单数量 - 入库数量”（入库缺失视为0）
    if (responseData.order_quantity !== undefined) {
      const oq = Number(responseData.order_quantity);
      const iq = Number(responseData.inbound_quantity ?? 0);
      responseData.to_complete_quantity = recomputeUnifiedToComplete(oq, iq);
      if (debugEnabled) debugInfo.recomputed = { order_quantity: oq, inbound_quantity: Number(isNaN(iq) ? 0 : iq), to_complete_quantity: responseData.to_complete_quantity };
    }

    // 若关键字段缺失，按需做一次简短详情增强（不传 detail=1 也会触发，但超时严格）
  const needEnrich = (!responseData.material_code || !responseData.product_name || responseData.order_quantity === undefined || responseData.inbound_quantity === undefined || responseData.to_complete_quantity === undefined);
    if (needEnrich && String(detail) !== '1') {
      const orderId = src.id || src.autoid || src.orderId;
      if (orderId) {
        const d = await productionOrderService.getOrderDetail(orderId, { timeoutMs: 12000 }); // 增加到12秒
        if (d.success && d.data) {
          const dsrc = d.data;
          const dProducts = dsrc.OrderProduct || dsrc.orderProduct || [];
          const dop = Array.isArray(dProducts) && dProducts.length > 0 ? dProducts[0] : {};
          const dDefine = dsrc.productDefineDts || {};
          responseData.material_code = firstNonEmpty(responseData.material_code, dop.productCode, dop.productId?.code);
          responseData.product_name = firstNonEmpty(responseData.product_name, dop.productName, dop.productId?.name);
          responseData.demand_category = firstNonEmpty(responseData.demand_category, dDefine.XS11);
          responseData.customer_number = firstNonEmpty(responseData.customer_number, dDefine.XS15, dsrc.customer?.code, dsrc.customerName);
          responseData.order_quantity = firstNonNullNum(responseData.order_quantity, dop.quantity, dop.qty);
          responseData.unit_name = firstNonEmpty(responseData.unit_name, dop.unitName, dop.productId?.measureUnit?.name);
          // 明细中补齐入库、累计完工与待完工（统一用辅助函数）
          const dInbound = computeInboundCandidate(dop, dsrc);
          const dCompletedTotal = computeCompletedTotalCandidate(dop, dsrc, dInbound);
          if (responseData.inbound_quantity === undefined && dInbound !== undefined) {
            responseData.inbound_quantity = dInbound;
          }
          if (responseData.completed_total_quantity === undefined && dCompletedTotal !== undefined) {
            responseData.completed_total_quantity = dCompletedTotal;
          }
          // 待完工数量继续遵循：订单数量 - 入库数量（入库缺失视为0）
          if (responseData.order_quantity !== undefined) {
            const baseInbound = responseData.inbound_quantity ?? dInbound ?? 0;
            responseData.to_complete_quantity = computeToComplete(responseData.order_quantity, baseInbound);
          }
        }
      }
    }

    // 可选详情增强：仅在明确请求时调用，设置更短超时
    if (String(detail) === '1') {
      const orderId = src.id || src.autoid || src.orderId;
      if (orderId) {
        const d = await productionOrderService.getOrderDetail(orderId, { timeoutMs: 5000 });
        if (d.success && d.data) {
          const dsrc = d.data;
          const dProducts = dsrc.OrderProduct || dsrc.orderProduct || [];
          const dop = Array.isArray(dProducts) && dProducts.length > 0 ? dProducts[0] : {};
          const dDefine = dsrc.productDefineDts || {};
          responseData.material_code = firstNonEmpty(responseData.material_code, dop.productCode, dop.productId?.code);
          responseData.product_name = firstNonEmpty(responseData.product_name, dop.productName, dop.productId?.name);
          responseData.demand_category = firstNonEmpty(responseData.demand_category, dDefine.XS11);
          responseData.customer_number = firstNonEmpty(responseData.customer_number, dDefine.XS15, dsrc.customer?.code);
          responseData.order_quantity = firstNonNullNum(responseData.order_quantity, dop.quantity, dop.qty);
          responseData.unit_name = firstNonEmpty(responseData.unit_name, dop.unitName, dop.productId?.measureUnit?.name);
        }
      }
    }

  // 写入缓存
  setCachedOrder(cleaned, responseData);

    clearTimeout(slowWarnTimer);
  const out = { success: true, data: responseData, cache: false };
  if (debugEnabled) out.debug = debugInfo;
  return res.json(out);
  } catch (error) {
    clearTimeout(slowWarnTimer);
    console.error('获取订单信息失败:', error);
    return res.json({ success: false, error: '获取订单信息失败', details: error.message || String(error) });
  }
});

// 兼容前端旧路径：/api/yonyou/order/:order_number
// 返回字段与前端 entry.vue 期望一致：materialCode, materialName, productDefineDts__XS11
app.get('/api/yonyou/order/:order_number', async (req, res) => {
  try {
    const { order_number } = req.params;
    const cleaned = cleanOrderNumber(order_number);

    // 按订单号查询（默认窗口）
    let listResult = await productionOrderService.getProductionOrders({
      pageIndex: 1,
      pageSize: 1,
      code: cleaned,
      requestTimeoutMs: 8000
    });

    let orders = listResult.success ? (listResult.data?.orders || []) : [];
    if (orders.length === 0) {
      // 扩大窗口
      listResult = await productionOrderService.getProductionOrders({
        pageIndex: 1,
        pageSize: 1,
        code: cleaned,
        startDate: '2000-01-01',
        endDate: '2099-12-31',
        requestTimeoutMs: 12000
      });
      orders = listResult.success ? (listResult.data?.orders || []) : [];
    }

    if (orders.length === 0) {
      // 返回空数据以触发前端 warning 分支
      return res.json(null);
    }

    const src = orders[0];
    const orderProducts = src.OrderProduct || src.orderProduct || [];
    const op = Array.isArray(orderProducts) && orderProducts.length > 0 ? orderProducts[0] : {};
    const productDefineDts = src.productDefineDts || {};

    const data = {
      orderNumber: src.code || cleaned,
      materialCode: op.productCode || op.productId?.code || '',
      materialName: op.productName || op.productId?.name || '',
      productDefineDts__XS11: productDefineDts.XS11 || src.transTypeName || src.transType?.name || ''
    };

    return res.json(data);
  } catch (error) {
    console.error('兼容订单查询失败:', error.message || error);
    // 返回空数据触发前端 warning
    return res.json(null);
  }
});

// 获取异常类别列表
app.get('/api/abnormality-categories', async (req, res) => {
  try {
    const rows = await dbPool.query(
      "SELECT * FROM abnormality_categories WHERE is_active = 1 ORDER BY category_name"
    );
    res.json({ categories: rows });
  } catch (err) {
    console.error('获取异常类别列表失败:', err);
    res.status(500).json({ error: err.message });
  }
});

// 添加异常类别
app.post('/api/abnormality-categories', async (req, res) => {
  const { category_name, description } = req.body;

  if (!category_name) {
    return res.status(400).json({ error: '异常类别名称不能为空' });
  }

  try {
    const result = await dbPool.query(
      `INSERT INTO abnormality_categories (category_name, description, is_active, created_at, updated_at)
       VALUES (?, ?, 1, datetime('now'), datetime('now'))`,
      [category_name, description || '']
    );

    res.json({
      success: true,
      category_id: result.lastInsertRowid,
      message: '异常类别添加成功'
    });
  } catch (err) {
    console.error('添加异常类别错误:', err);
    if (err.message.includes('UNIQUE constraint failed')) {
      res.status(400).json({ error: '异常类别已存在' });
    } else {
      res.status(500).json({ error: err.message });
    }
  }
});

// 删除异常类别
app.delete('/api/abnormality-categories/:id', async (req, res) => {
  const { id } = req.params;

  if (!id || isNaN(id)) {
    return res.status(400).json({ error: '无效的异常类别ID' });
  }

  try {
    const result = await dbPool.query(
      "UPDATE abnormality_categories SET is_active = 0, updated_at = datetime('now') WHERE id = ?",
      [id]
    );

    if (result.changes === 0) {
      res.status(404).json({ error: '异常类别不存在' });
    } else {
      res.json({ success: true, message: '异常类别删除成功' });
    }
  } catch (err) {
    console.error('删除异常类别错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// 更新异常类别描述（仅允许修改描述，不允许修改名称）
app.put('/api/abnormality-categories/:id/description', async (req, res) => {
  const { id } = req.params;
  const { description } = req.body;

  if (!id || isNaN(id)) {
    return res.status(400).json({ error: '无效的异常类别ID' });
  }

  try {
    const result = await dbPool.query(
      "UPDATE abnormality_categories SET description = ?, updated_at = datetime('now') WHERE id = ?",
      [description || '', id]
    );

    if (result.changes > 0) {
      res.json({
        success: true,
        message: '异常类别描述更新成功',
        data: { id, description }
      });
    } else {
      res.status(404).json({ error: '异常类别不存在' });
    }
  } catch (err) {
    console.error('更新异常类别描述错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// 更新异常类别状态
app.put('/api/abnormality-categories/:id/status', async (req, res) => {
  const { id } = req.params;
  const { is_active } = req.body;

  if (!id || isNaN(id)) {
    return res.status(400).json({ error: '无效的异常类别ID' });
  }

  if (typeof is_active !== 'boolean') {
    return res.status(400).json({ error: '状态值必须为布尔类型' });
  }

  try {
    const result = await dbPool.query(
      "UPDATE abnormality_categories SET is_active = ?, updated_at = datetime('now') WHERE id = ?",
      [is_active ? 1 : 0, id]
    );

    if (result.changes > 0) {
      res.json({
        success: true,
        message: `异常类别已${is_active ? '启用' : '禁用'}`,
        data: { id, is_active }
      });
    } else {
      res.status(404).json({ error: '异常类别不存在' });
    }
  } catch (err) {
    console.error('更新异常类别状态错误:', err);
    res.status(500).json({ error: err.message });
  }
});

// 获取PPH目标值列表 (用于目标值管理页面)
app.get('/api/targets', (req, res) => {
  console.log('收到目标值查询请求，查询参数:', req.query);

  const { line_name, material_code, product_name, product_series, record_date, created_date } = req.query;

  let sql = `
    SELECT * FROM pph_targets
    WHERE (is_active = 1 OR is_active IS NULL)
  `;
  const params = [];

  if (line_name) {
    sql += ' AND line_name = ?';
    params.push(line_name);
  }

  if (material_code) {
    sql += ' AND material_code LIKE ?';
    params.push(`%${material_code}%`);
  }

  if (product_name) {
    sql += ' AND product_name LIKE ?';
    params.push(`%${product_name}%`);
  }

  if (product_series) {
    sql += ' AND product_series LIKE ?';
    params.push(`%${product_series}%`);
  }

  if (record_date) {
    // 兼容日期输入被规范化成字符串的情况
    sql += ' AND record_date = ?';
    params.push(String(record_date));
  }

  if (created_date) {
    sql += ' AND DATE(created_at) = ?';
    params.push(created_date);
  }

  sql += ' ORDER BY record_date DESC, material_code, line_name';

  console.log('执行查询:', sql, '参数:', params);

  dbPool.query(sql, params).then(rows => {
    console.log('查询到目标值数据:', rows ? rows.length : 'null', '条');
    res.json({
      success: true,
      data: rows || [],
      total: rows ? rows.length : 0
    });
  }).catch(err => {
    console.error('查询目标值失败:', err);
    res.status(500).json({
      success: false,
      error: err.message
    });
  });
});

// 创建PPH目标值 (用于前端录入) - 增强版本（保存后自动清缓存；目标值按公式计算）
app.post('/api/targets', enhancedMiddleware.validateInput('pph_target'), async (req, res) => {
  const {
    material_code,
    line_name,
    product_name,
    product_series,
    record_date,
    takt_time,
    motor_takt_time,
    // 以下两个字段前端只显示不编辑，后端统一按公式计算，忽略入参
    whole_machine_target,
    motor_target,
    standard_staff
  } = req.body;

  // 验证必填字段（节拍/电机节拍允许为空）
  if (!material_code || !line_name || !product_name || !record_date || !standard_staff) {
    res.status(400).json({
      error: '缺少必填字段: material_code, line_name, product_name, record_date, standard_staff'
    });
    return;
  }

  try {
    // 先检查是否已存在（用于返回 updated 标志）
    const existing = await dbPool.query(
      `SELECT id FROM pph_targets
       WHERE material_code = ? AND record_date = ? AND line_name = ? AND (is_active = 1 OR is_active IS NULL)
       LIMIT 1`,
      [material_code, record_date, line_name]
    );
    const isUpdate = Array.isArray(existing) && existing.length > 0;

    // 规范化输入（允许节拍/电机节拍为空）
    let _takt = null;
    if (takt_time !== undefined && takt_time !== null && takt_time !== '') {
      const n = Number(takt_time);
      if (!Number.isNaN(n)) _takt = Math.round(n * 100) / 100;
    }
    let _motorTakt = null;
    if (motor_takt_time !== undefined && motor_takt_time !== null && motor_takt_time !== '') {
      const m = Number(motor_takt_time);
      if (!Number.isNaN(m)) _motorTakt = Math.round(m * 100) / 100;
    }
    const _staff = Number(standard_staff);

    // 按公式计算：PPH = 3600 / (人数 * 节拍)
    const computedWhole = (_staff > 0 && _takt > 0) ? Math.round((3600 / (_staff * _takt)) * 100) / 100 : null;
    const computedMotor = (_staff > 0 && _motorTakt && _motorTakt > 0) ? Math.round((3600 / (_staff * _motorTakt)) * 100) / 100 : null;

    // 使用连接池执行 UPSERT（新增 motor_takt_time 列）
    const result = await dbPool.query(
      `INSERT OR REPLACE INTO pph_targets
       (material_code, product_name, product_series, record_date, takt_time, motor_takt_time,
        whole_machine_target, motor_target, standard_staff, line_name, is_active)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)`,
      [
        material_code,
        product_name,
        product_series || null,
        record_date,
        _takt,
        _motorTakt,
        computedWhole, // 忽略入参 whole_machine_target，按公式计算
        computedMotor, // 忽略入参 motor_target，按公式计算
        _staff,
        line_name
      ]
    );

    // 保存成功后，清理与目标相关的缓存，确保后续查询/重算立即生效
    try { targetCache.clear(); averageCache.clear(); } catch(_) {}

    res.json({
      success: true,
      updated: isUpdate,
      target_id: result && result.lastInsertRowid,
      message: isUpdate ? 'PPH目标值更新成功' : 'PPH目标值保存成功',
      // 回传服务端最终计算/规范化后的关键字段，便于前端即时展示
      takt_time: _takt,
      motor_takt_time: _motorTakt,
      standard_staff: _staff,
      whole_machine_target: computedWhole,
      motor_target: computedMotor
    });
  } catch (err) {
    console.error('保存PPH目标值失败:', err);
    res.status(500).json({ error: err.message || String(err) });
  }
});

// 删除PPH目标值
app.delete('/api/targets/:id', async (req, res) => {
  const { id } = req.params;

  try {
    const result = await dbPool.query(
      "UPDATE pph_targets SET is_active = 0 WHERE id = ?",
      [id]
    );
    if (result.changes === 0) {
      res.status(404).json({ error: '目标值不存在' });
    } else {
      res.json({ success: true, message: '目标值删除成功' });
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});


// 更新PPH目标值（逐行保存，行内编辑）- 按公式自动重算目标值，并清缓存
app.put('/api/targets/:id', async (req, res) => {
  const { id } = req.params;
  try {
    // 允许更新字段（去除手动修改目标值），新增 motor_takt_time
    const allowed = new Set(['product_series','record_date','takt_time','motor_takt_time','standard_staff']);
    const body = req.body || {};

    // 将可为小数的数值统一保留两位小数
    if (body && typeof body === 'object') {
      ['takt_time','motor_takt_time'].forEach((k)=>{
        if (Object.prototype.hasOwnProperty.call(body, k) && body[k] !== '' && body[k] !== null && body[k] !== undefined) {
          const num = Number(body[k]);
          if (!isNaN(num)) body[k] = Math.round(num * 100) / 100;
        }
      });
    }

    // 读取现有行（用于计算未提供字段时的目标值）
    const currentRows = await dbPool.query(
      'SELECT takt_time, motor_takt_time, standard_staff FROM pph_targets WHERE id = ? AND (is_active = 1 OR is_active IS NULL) LIMIT 1',
      [id]
    );
    if (!currentRows || currentRows.length === 0) {
      return res.status(404).json({ success: false, error: '目标值不存在' });
    }
    const curr = currentRows[0] || {};

    // 计算目标值（优先使用入参；若入参为null/''视为清空）
    const hasTakt = Object.prototype.hasOwnProperty.call(body, 'takt_time');
    const hasMotorTakt = Object.prototype.hasOwnProperty.call(body, 'motor_takt_time');
    const hasStaff = Object.prototype.hasOwnProperty.call(body, 'standard_staff');

    const effTakt = hasTakt
      ? ((body.takt_time === null || body.takt_time === '' || Number.isNaN(Number(body.takt_time))) ? null : Number(body.takt_time))
      : (curr.takt_time === null || curr.takt_time === undefined ? null : Number(curr.takt_time));

    const effMotorTakt = hasMotorTakt
      ? ((body.motor_takt_time === null || body.motor_takt_time === '' || Number.isNaN(Number(body.motor_takt_time))) ? null : Number(body.motor_takt_time))
      : (curr.motor_takt_time === null || curr.motor_takt_time === undefined ? null : Number(curr.motor_takt_time));

    const effStaff = hasStaff
      ? ((body.standard_staff === null || body.standard_staff === '' || Number.isNaN(Number(body.standard_staff))) ? null : Number(body.standard_staff))
      : (curr.standard_staff === null || curr.standard_staff === undefined ? null : Number(curr.standard_staff));

    const computedWhole = (effStaff > 0 && effTakt > 0) ? Math.round((3600 / (effStaff * effTakt)) * 100) / 100 : null;
    const computedMotor = (effStaff > 0 && effMotorTakt > 0) ? Math.round((3600 / (effStaff * effMotorTakt)) * 100) / 100 : null;

    const fields = [];
    const params = [];

    for (const k of Object.keys(body)) {
      if (allowed.has(k)) {
        fields.push(`${k} = ?`);
        const v = body[k];
        params.push(v === '' ? null : v);
      }
    }

    // 无论是否传入，目标值均按公式回填
    fields.push('whole_machine_target = ?');
    params.push(computedWhole);
    fields.push('motor_target = ?');
    params.push(computedMotor);

    fields.push('updated_at = CURRENT_TIMESTAMP');

    const sql = `UPDATE pph_targets SET ${fields.join(', ')} WHERE id = ? AND (is_active = 1 OR is_active IS NULL)`;
    params.push(id);

    try {
      const result = await dbPool.query(sql, params);
      if (!result || result.changes === 0) {
        return res.status(404).json({ success: false, error: '目标值不存在或未发生变化' });
      }
    } catch (e) {
      if (/(UNIQUE|unique)/i.test(e.message || '')) {
        return res.status(409).json({ success: false, error: '该物料+生产线在该记录日期已存在目标值（唯一约束冲突）' });
      }
      throw e;
    }

    // 更新成功后清缓存
    try { targetCache.clear(); averageCache.clear(); } catch(_) {}

    res.json({ success: true, id, updated: true, message: '目标值更新成功',
    // 回传重算后的值，前端可直接刷新当前行
    whole_machine_target: computedWhole,
    motor_target: computedMotor,
    takt_time: effTakt,
    motor_takt_time: effMotorTakt,
    standard_staff: effStaff
  });
  } catch (err) {
    console.error('更新PPH目标值失败:', err);
    res.status(500).json({ success: false, error: err.message || String(err) });
  }
});

// (已合并) Excel模板下载的固定路由已移除，统一使用 /api/excel/template/:type

// Excel模板下载 - 目标值
app.get('/api/excel/template/:type', (req, res) => {
  const { type } = req.params;

  try {
    let workbook;
    let filename;

    if (type === 'targets') {
      workbook = excelImportService.generateTargetTemplate();
      filename = 'PPH目标值导入模板.xlsx';
    } else if (type === 'estimates') {
      workbook = excelImportService.generateEstimateTemplate();
      filename = 'PPH预估值导入模板.xlsx';
    } else if (type === 'records') {
      workbook = excelImportService.generateRecordsTemplate();
      filename = 'PPH记录历史导入模板.xlsx';
    } else {
      res.status(400).json({ error: '不支持的模板类型' });
      return;
    }

    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
    res.setHeader('Cache-Control', 'no-cache');

    // 生成Excel文件并发送
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    res.end(buffer);

  } catch (error) {
    console.error('生成Excel模板失败:', error);
    res.status(500).json({ error: '生成模板失败' });
  }
});

// 更新单条记录：限制可编辑字段；更新后重算派生值
app.put('/api/records/:id', express.json(), async (req, res) => {
  try {
    const { id } = req.params;
    const input = req.body || {};

    // 不允许直接编辑的字段（含派生字段）
    const banned = new Set([
      'order_number','material_code','product_name','demand_category','customer_number','line_name',
      'order_quantity','inbound_quantity','to_complete_quantity',
      'calculated_pph','efficiency_rate','target_pph','target_record_date','estimate_pph',
      'total_people','net_working_minutes','actual_takt_seconds',
      'target_takt_time','target_motor_takt_time', // 节拍字段也是派生字段，不允许直接编辑
      'id','created_at','updated_at','created_by','sync_status'
    ]);

    // 读当前行
    const currentRows = await dbPool.query('SELECT * FROM pph_records WHERE id = ?', [id]);
    const current = currentRows[0];
    if (!current) {
      res.status(404).json({ error: '未找到记录' });
      return;
    }

    // 获取表结构，过滤只更新存在且未禁止的列
    const connection = await dbPool.getConnection();
    const columns = await new Promise((resolve, reject) => {
      connection.all('PRAGMA table_info(pph_records)', [], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
    dbPool.releaseConnection(connection);
    console.log('🔍 调试 - columns:', columns);
    const existing = new Set(Array.isArray(columns) ? columns.map(c => c.name) : []);
    const patch = {};
    Object.keys(input).forEach(k => {
      if (!banned.has(k) && existing.has(k)) patch[k] = input[k];
    });

    // 应用更新（原始字段）
    if (Object.keys(patch).length > 0) {
      const fields = Object.keys(patch).map(k => `${k} = ?`);
      const values = Object.keys(patch).map(k => patch[k]);
      fields.push('updated_at = CURRENT_TIMESTAMP');
      const sql = `UPDATE pph_records SET ${fields.join(', ')} WHERE id = ?`;
      values.push(id);
      await dbPool.query(sql, values);
    }

    // 重新读取合并后的行
    const rows = await dbPool.query('SELECT * FROM pph_records WHERE id = ?', [id]);
    const row = rows[0];

    // 计算派生字段
    function toInt(v){ const n = Number(v); return isNaN(n)?0:n; }

    // 1. 计算总人数（线上人员 + 线外人员）
    const workingPeople = toInt(row.working_people);
    const offlineStaff = toInt(row.offline_staff);
    const totalPeople = Math.max(1, workingPeople + offlineStaff);

    // 2. 计算时间相关
    const start_time = row.start_time || '00:00';
    const end_time = row.end_time || '00:00';
    const [sH,sM] = String(start_time).split(':').map(x=>parseInt(x,10));
    const [eH,eM] = String(end_time).split(':').map(x=>parseInt(x,10));
    const sMin = (isNaN(sH)||isNaN(sM))?0:(sH*60+sM);
    let eMin = (isNaN(eH)||isNaN(eM))?0:(eH*60+eM);
    if (eMin < sMin) eMin += 24*60;
    const totalMinutes = Math.max(0, eMin - sMin);

    // 3. 计算净工作时间（总时间 - 休息时间 - 异常损失时间 - 换线时间 - 维护时间）
    const breakMin = toInt(row.break_minutes);
    const abnormalMin = toInt(row.abnormal_loss_minutes);
    const changeMin = toInt(row.changeover_minutes);
    const maintMin = toInt(row.maintenance_time);
    const netMinutes = Math.max(0, totalMinutes - (breakMin + abnormalMin + changeMin + maintMin));
    const netHours = netMinutes / 60;

    // 4. 计算实际PPH（动态数量列：优先 quantity，其次 completed_quantity）
    const qtyRaw = (row.quantity != null ? Number(row.quantity) : (row.completed_quantity != null ? Number(row.completed_quantity) : 0)) || 0;
    const calculated_pph = (netHours > 0 && totalPeople > 0) ? Math.round((qtyRaw / (netHours * totalPeople)) * 100) / 100 : 0;

    // 5. PPH平均值字段已移除

    // 5. 计算实际节拍（秒/件）
    const actual_takt_seconds = (calculated_pph > 0) ? Math.round((3600 / calculated_pph) * 100) / 100 : 0;

    // 查找目标/预估并计算达成率
    let target_pph = null, target_record_date = null, estimate_pph = null, efficiency_rate = null;
    if (row.material_code && row.line_name && row.record_date) {
      const p = [row.material_code, row.line_name, row.record_date];
      const qPrev = `SELECT record_date, whole_machine_target, motor_target, takt_time, motor_takt_time FROM pph_targets
                     WHERE UPPER(material_code)=UPPER(?) AND TRIM(line_name)=TRIM(?) AND record_date <= ? AND (is_active = 1 OR is_active IS NULL)
                     ORDER BY record_date DESC LIMIT 1`;
      const prev = await dbPool.query(qPrev, p).then(rows => rows[0] || null);
      if (prev) {
        target_record_date = prev.record_date;
        target_pph = (row.is_whole_machine === 1)
          ? (prev.whole_machine_target != null ? Number(prev.whole_machine_target) : null)
          : (prev.motor_target != null ? Number(prev.motor_target) : null);

        // 🆕 同时获取节拍字段
        row.target_takt_time = prev.takt_time != null ? Number(prev.takt_time) : null;
        row.target_motor_takt_time = prev.motor_takt_time != null ? Number(prev.motor_takt_time) : null;
      }
      const est = await dbPool.query("SELECT estimate_pph FROM pph_estimates WHERE UPPER(material_code)=UPPER(?) AND TRIM(line_name)=TRIM(?)", [row.material_code, row.line_name]).then(rows => rows[0] || null);
      if (est && est.estimate_pph != null) estimate_pph = Number(est.estimate_pph);
      const baseline = (target_pph!=null && target_pph>0) ? target_pph : ((estimate_pph!=null && estimate_pph>0)?estimate_pph:null);
      efficiency_rate = (baseline!=null && baseline>0) ? Math.round(((calculated_pph/baseline)*100)*100)/100 : null;
    }

    // 写回派生字段（补充节拍字段持久化）
    const sql = `UPDATE pph_records SET
      total_people = ?,
      net_working_minutes = ?,
      calculated_pph = ?,
      actual_takt_seconds = ?,
      target_pph = ?,
      target_record_date = ?,
      target_takt_time = ?,
      target_motor_takt_time = ?,
      estimate_pph = ?,
      efficiency_rate = ?,
      updated_at = CURRENT_TIMESTAMP
      WHERE id = ?`;
    await dbPool.query(sql, [
      totalPeople,
      netMinutes,
      calculated_pph,
      actual_takt_seconds,
      target_pph,
      target_record_date,
      row.target_takt_time ?? null,
      row.target_motor_takt_time ?? null,
      estimate_pph,
      efficiency_rate,
      id
    ]);

    const updatedRows = await dbPool.query('SELECT * FROM pph_records WHERE id = ?', [id]);
    const updatedRow = updatedRows[0];
    res.json({ success:true, data: updatedRow });
  } catch (error) {
    console.error('更新失败:', error);
    res.status(500).json({ success:false, error: error.message });
  }
});

// 删除单条记录
app.delete('/api/records/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const result = await dbPool.query('DELETE FROM pph_records WHERE id = ?', [id]);
    if (result.changes === 0) {
      res.status(404).json({ success: false, error: '未找到记录' });
    } else {
      res.json({ success: true, deleted: result.changes });
    }
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

// Excel文件导入
app.post('/api/excel/import/:type', (req, res) => {
  const { type } = req.params;

  console.log('Excel导入请求:', type, req.headers['content-type']);

  // 使用upload中间件
  upload.single('file')(req, res, async (err) => {
    if (err) {
      console.error('文件上传错误:', err);
      res.status(400).json({ error: '文件上传失败: ' + err.message });
      return;
    }

    if (!req.file) {
      res.status(400).json({ error: '请选择要导入的Excel文件' });
      return;
    }

    console.log('上传的文件:', req.file.originalname, req.file.path);

    try {
      let result;
      let impacted = null;

      if (type === 'targets') {
        result = await excelImportService.importPPHTargets(req.file.path);
        impacted = result && result.impacted;
      } else if (type === 'estimates') {
        result = await excelImportService.importPPHEstimates(req.file.path);
        impacted = result && result.impacted;
      } else if (type === 'records') {
        result = await excelImportService.importPPHRecords(req.file.path);
        impacted = result && result.impacted;
      } else {
        res.status(400).json({ error: '不支持的导入类型' });
        return;
      }

      // 优化：导入后提供重算选项，而非自动触发（避免资源浪费和性能问题）
      try {
        // 检查是否有影响范围数据，如果有则提供重算建议
        if (impacted && (impacted.line_names?.length || impacted.material_codes?.length || impacted.from_date || impacted.to_date)) {
          const recomputePayload = {
            line_names: impacted.line_names,
            material_codes: impacted.material_codes,
            from_date: impacted.from_date,
            to_date: impacted.to_date,
            recalc_pph: 1,
            batch_size: parseInt(process.env.RECOMPUTE_BATCH_SIZE || '500')
          };

          // 估算影响的记录数量
          const { buildWhereClauseForRecompute } = require('./recompute/jobs.cjs');
          const { whereSql, params } = buildWhereClauseForRecompute(recomputePayload);
          const totalRow = await dbPool.query(`SELECT COUNT(1) AS cnt FROM pph_records ${whereSql}`, params).then(r=>r&&r[0]||{cnt:0});
          const estimatedRecords = Number(totalRow.cnt||0);

          console.log('📊 导入影响范围分析:', {
            line_names: impacted.line_names,
            material_codes: impacted.material_codes,
            date_range: `${impacted.from_date} ~ ${impacted.to_date}`,
            estimated_records: estimatedRecords
          });

          result.recomputeSuggestion = {
            recommended: estimatedRecords > 0,
            payload: recomputePayload,
            estimatedRecords,
            message: estimatedRecords > 0
              ? `建议重算 ${estimatedRecords} 条受影响的记录以更新目标值和预估值`
              : '未发现需要重算的记录'
          };
        } else {
          result.recomputeSuggestion = {
            recommended: false,
            message: '导入数据未影响现有记录，无需重算'
          };
        }
      } catch (e) {
        console.warn('导入后重算分析失败:', e && (e.message || e));
        result.recomputeSuggestion = {
          recommended: false,
          error: e && (e.message || e),
          message: '重算分析失败，请手动检查是否需要重算'
        };
      }

      res.json(result);

    } catch (error) {
      console.error('Excel导入失败:', error);
      res.status(500).json({ error: '导入失败: ' + error.message });
    }
  });

});

// 新增：手动启动导入后重算任务
app.post('/api/import/start-recompute', async (req, res) => {
  try {
    const { payload } = req.body;

    if (!payload || !dbPool) {
      return res.status(400).json({
        success: false,
        error: '缺少重算参数或数据库未初始化'
      });
    }

    console.log('🔄 手动启动导入后重算任务:', payload);

    const { startRecomputeJob } = require('./recompute/jobs.cjs');
    const jobInfo = await startRecomputeJob(payload, dbPool);

    res.json({
      success: true,
      job_id: jobInfo.job_id,
      total: jobInfo.total,
      message: `已启动重算任务，预计处理 ${jobInfo.total} 条记录`
    });

  } catch (error) {
    console.error('启动重算任务失败:', error);
    res.status(500).json({
      success: false,
      error: '启动重算任务失败',
      details: error.message
    });
  }
});

// 速率限制器（优化为高性能模式）
class RateLimiter {
  constructor(maxRequests = 1000, timeWindow = 60000) { // 每分钟1000次请求（大幅提升）
    this.requests = [];
    this.maxRequests = maxRequests;
    this.timeWindow = timeWindow;
  }

  async wait() {
    const now = Date.now();
    // 清理过期请求
    this.requests = this.requests.filter(time => now - time < this.timeWindow);

    if (this.requests.length >= this.maxRequests) {
      const oldestRequest = this.requests[0];
      const waitTime = this.timeWindow - (now - oldestRequest);
      if (waitTime > 0) {
        console.log(`⏳ 速率限制：等待 ${Math.ceil(waitTime/1000)} 秒...`);
        await new Promise(resolve => setTimeout(resolve, waitTime));
      }
    }

    this.requests.push(now);
  }
}

const serverRateLimiter = new RateLimiter(50, 60000); // 每分钟50次请求

// 🚀 高性能直接数据库查询（避免HTTP调用开销）
async function getTargetAndEstimateForRecordDirect(materialCode, lineName, recordDate, isWholeMachine) {
  try {
    let target_pph = null;
    let target_record_date = null;
    let estimate_pph = null;

    // 直接查询目标值
    const targetQuery = `
      SELECT target_pph, record_date
      FROM pph_targets
      WHERE material_code = ? AND line_name = ? AND is_whole_machine = ?
        AND record_date <= ?
      ORDER BY record_date DESC
      LIMIT 1
    `;
    const targetResult = await dbPool.query(targetQuery, [materialCode, lineName, isWholeMachine ? 1 : 0, recordDate]);
    if (targetResult && targetResult[0]) {
      target_pph = targetResult[0].target_pph;
      target_record_date = targetResult[0].record_date;
    }

    // 直接查询预估值
    const estimateQuery = `
      SELECT estimate_pph
      FROM pph_estimates
      WHERE material_code = ? AND line_name = ? AND is_whole_machine = ?
        AND record_date <= ?
      ORDER BY record_date DESC
      LIMIT 1
    `;
    const estimateResult = await dbPool.query(estimateQuery, [materialCode, lineName, isWholeMachine ? 1 : 0, recordDate]);
    if (estimateResult && estimateResult[0]) {
      estimate_pph = estimateResult[0].estimate_pph;
    }

    return { target_pph, target_record_date, estimate_pph };
  } catch (error) {
    console.warn(`⚠️ 直接查询失败: ${error.message}`);
    return { target_pph: null, target_record_date: null, estimate_pph: null };
  }
}

// 直接调用现有API端点获取目标值和预估值（避免重复代码）
async function getTargetAndEstimateForRecord(materialCode, lineName, recordDate, isWholeMachine) {
  const maxRetries = 3;
  let retryCount = 0;

  while (retryCount < maxRetries) {
    try {
      // 应用速率限制
      await serverRateLimiter.wait();

      // 使用内部HTTP调用现有的API端点，避免重复代码
      const axios = require('axios');
      const API_BASE = 'http://127.0.0.1:3011/api';

      const response = await axios.get(`${API_BASE}/targets/pph`, {
        params: {
          material_code: materialCode,
          line_name: lineName,
          record_date: recordDate,
          whole_machine: isWholeMachine ? 'true' : 'false'
        },
        timeout: 10000 // 增加到10秒超时
      });

      if (response.data) {
        return {
          target_pph: response.data.target_pph,
          estimate_pph: response.data.estimate_pph,
          target_record_date: response.data.target_record_date
        };
      }

      return { target_pph: null, estimate_pph: null, target_record_date: null };
    } catch (error) {
      retryCount++;
      const errorMsg = error && (error.message || error);

      if (error.response && error.response.status === 429) {
        // 429错误：速率限制
        const retryAfter = error.response.headers['retry-after'] || 60;
        console.warn(`⚠️ 速率限制 (429)，第${retryCount}次重试，等待${retryAfter}秒...`);
        await new Promise(resolve => setTimeout(resolve, retryAfter * 1000));
      } else if (retryCount < maxRetries) {
        // 其他错误：指数退避
        const backoffTime = Math.min(1000 * Math.pow(2, retryCount), 10000);
        console.warn(`⚠️ API调用失败，第${retryCount}次重试，等待${backoffTime}ms...`, errorMsg);
        await new Promise(resolve => setTimeout(resolve, backoffTime));
      } else {
        console.warn(`调用目标值API失败: ${errorMsg}`);
        // 如果API调用失败，回退到直接数据库查询（保持系统稳定性）
        return await fallbackDirectQuery(materialCode, lineName, recordDate, isWholeMachine);
      }
    }
  }

  // 如果所有重试都失败，回退到直接数据库查询
  return await fallbackDirectQuery(materialCode, lineName, recordDate, isWholeMachine);
}

// 回退查询函数（当API调用失败时使用）
async function fallbackDirectQuery(materialCode, lineName, recordDate, isWholeMachine) {
  try {
    console.log('🔄 使用回退查询方式...');

    // 目标值查询（与现有API完全一致的逻辑）
    const queryPrev = `SELECT * FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date <= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date DESC
                       LIMIT 1`;
    const queryNext = `SELECT * FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date >= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date ASC
                       LIMIT 1`;

    const params = [materialCode, lineName, recordDate];
    const targetRowPrevResults = await dbPool.query(queryPrev, params);
    const targetRowPrev = targetRowPrevResults[0] || null;

    let useRow = targetRowPrev;
    if (!useRow) {
      const targetRowNextResults = await dbPool.query(queryNext, params);
      useRow = targetRowNextResults[0] || null;
    }

    // 预估值计算（基于历史数据动态计算）
    const estimateRow = await calculateEstimatePPH(materialCode, lineName);
    console.log('📈 动态计算预估PPH:', estimateRow);

    let targetPPH = null;
    if (useRow) {
      targetPPH = isWholeMachine ? useRow.whole_machine_target : useRow.motor_target;
    }
    const estimatePPH = estimateRow ? estimateRow.estimate_pph : null;

    return {
      target_pph: targetPPH,
      estimate_pph: estimatePPH,
      target_record_date: useRow ? useRow.record_date : null
    };
  } catch (error) {
    console.warn(`回退查询失败: ${error.message}`);
    return { target_pph: null, estimate_pph: null, target_record_date: null };
  }
}



// 重算历史记录的目标值、目标日期、预估值与达成率（调用原有逻辑）
// 重算端点说明：
// - 仅用于“离线校正/批量校验”用途；
// - 默认为只基于历史目标/预估回填 target_pph/estimate_pph/efficiency_rate；
// - 当传入 recalc_pph=1 时，会按记录自身的时间/人数/损失重新计算 calculated_pph（不会造数）。
app.post('/api/records/recompute', express.json(), async (req, res) => {
  try {
    // 检查是否已有重算任务在运行
    if (recomputeProgress.isRunning) {
      return res.status(409).json({
        success: false,
        error: '已有重算任务正在运行，请等待完成后再试',
        progress: recomputeProgress
      });
    }

    console.log('🔄 开始重算PPH记录...');
    const { from_date, to_date, line_name, material_code, record_date, order_number, recalc_pph } = req.body || {};

    // 是否重算PPH值
    const doRecalc = String(recalc_pph || '0') === '1';

    // 初始化进度跟踪
    recomputeProgress.isRunning = true;
    recomputeProgress.current = 0;
    recomputeProgress.total = 0;
    recomputeProgress.currentRecord = null;
    recomputeProgress.startTime = Date.now();
    recomputeProgress.estimatedTimeRemaining = null;
    recomputeProgress.status = 'running';
    recomputeProgress.error = null;
    const where = [];
    const params = [];

    if (record_date) { where.push('record_date = ?'); params.push(record_date); }
    if (order_number) { where.push('TRIM(order_number) = TRIM(?)'); params.push(order_number); }
    if (from_date) { where.push('record_date >= ?'); params.push(from_date); }
    if (to_date) { where.push('record_date <= ?'); params.push(to_date); }
    if (line_name) { where.push('TRIM(line_name) = TRIM(?)'); params.push(line_name); }
    if (material_code) { where.push('UPPER(material_code) = UPPER(?)'); params.push(material_code); }

    // 兼容不同表结构：动态选择数量与人数字段，避免“no such column”
    const hasQty = await columnExists('pph_records', 'quantity');
    const hasCompletedQty = await columnExists('pph_records', 'completed_quantity');
    const qtyExpr = hasQty ? 'quantity' : (hasCompletedQty ? 'completed_quantity' : '0');

    const hasTotalPeople = await columnExists('pph_records', 'total_people');
    const hasOnline = await columnExists('pph_records', 'online_staff');
    const hasOffline = await columnExists('pph_records', 'offline_staff');
    const hasTotalStaff = await columnExists('pph_records', 'total_staff');
    const hasWorking = await columnExists('pph_records', 'working_people');
    let peopleExpr = '0';
    if (hasTotalPeople) peopleExpr = 'total_people';
    else if (hasOnline && hasOffline) peopleExpr = 'COALESCE(online_staff,0)+COALESCE(offline_staff,0)';
    else if (hasWorking && hasOffline) peopleExpr = 'COALESCE(working_people,0)+COALESCE(offline_staff,0)';
    else if (hasTotalStaff) peopleExpr = 'COALESCE(total_staff,0)';

    // 检查maintenance_time字段是否存在
    const hasMaintenanceTime = await columnExists('pph_records', 'maintenance_time');
    const maintenanceExpr = hasMaintenanceTime ? 'COALESCE(maintenance_time,0)' : '0';

    // 获取所有需要重算的记录（包含完整字段）
    const sqlSel = `SELECT id, record_date, line_name, material_code, is_whole_machine, calculated_pph,
                           start_time, end_time, ${qtyExpr} AS quantity, ${peopleExpr} AS total_people,
                           COALESCE(break_minutes,0) AS break_minutes,
                           COALESCE(abnormal_loss_minutes,0) AS abnormal_loss_minutes,
                           COALESCE(changeover_minutes,0) AS changeover_minutes,
                           ${maintenanceExpr} AS maintenance_time,
                           net_working_minutes, actual_takt_seconds
                    FROM pph_records ${where.length?('WHERE '+where.join(' AND ')):''}
                    ORDER BY record_date, line_name, material_code`;

    const rows = await dbPool.query(sqlSel, params);
    console.log(`📊 找到 ${rows.length} 条记录需要重算`);

    // 更新总数
    recomputeProgress.total = rows.length;

    let updated = 0;

    // 🚀 使用高性能重算处理器
    console.log('🚀 启动高性能重算处理器...');

    const result = await recomputeProcessor.recomputeRecords(
      rows,
      { recalcPph: doRecalc },
      (progress) => {
        // 更新进度
        recomputeProgress.current = progress.processed;
        recomputeProgress.percentage = progress.percentage;

        if (progress.processed % 100 === 0 || progress.processed === progress.total) {
          console.log(`📊 重算进度: ${progress.processed}/${progress.total} (${progress.percentage}%)`);
        }
      }
    );

    updated = result.success;

    console.log(`✅ 重算完成: 成功更新 ${updated}/${rows.length} 条记录`);

    // 更新进度状态为完成
    recomputeProgress.isRunning = false;
    recomputeProgress.status = 'completed';
    recomputeProgress.currentRecord = null;

    res.json({ success: true, updated, total: rows.length });
  } catch (error) {
    console.error('❌ 重算失败:', error);

    // 更新进度状态为错误
    recomputeProgress.isRunning = false;
    recomputeProgress.status = 'error';
    recomputeProgress.error = error.message;
    recomputeProgress.currentRecord = null;

    res.status(500).json({ success: false, error: '重算失败: ' + error.message });
  }
});

// === 分批异步重算：已迁移至 /backend/recompute/jobs.cjs ===

// 进度跟踪对象
const recomputeProgress = {
  isRunning: false,
  current: 0,
  total: 0,
  currentRecord: null,
  startTime: null,
  estimatedTimeRemaining: null,
  status: 'idle', // idle, running, completed, error
  error: null
};

// 获取重算进度API
app.get('/api/records/recompute/progress', (req, res) => {
  const progress = {
    ...recomputeProgress,
    percentage: recomputeProgress.total > 0 ? Math.round((recomputeProgress.current / recomputeProgress.total) * 100) : 0
  };

  // 计算预估剩余时间
  if (progress.isRunning && progress.current > 0 && progress.startTime) {
    const elapsed = Date.now() - progress.startTime;
    const avgTimePerRecord = elapsed / progress.current;
    const remaining = progress.total - progress.current;
    progress.estimatedTimeRemaining = Math.round((remaining * avgTimePerRecord) / 1000); // 秒
  }

  res.json(progress);
});

// 🚀 缓存统计API
app.get('/api/cache/stats', (req, res) => {
  try {
    const stats = {
      targetCache: targetCache.getStats(),
      averageCache: averageCache.getStats(),
      recomputeProcessor: recomputeProcessor.getStats(),
      timestamp: new Date().toISOString()
    };

    res.json({
      success: true,
      data: stats
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// 🚀 清理缓存API
app.post('/api/cache/clear', (req, res) => {
  try {
    const { type } = req.body;

    if (type === 'target' || type === 'all') {
      targetCache.clear();
    }

    if (type === 'average' || type === 'all') {
      averageCache.clear();
    }

    res.json({
      success: true,
      message: `缓存清理完成: ${type || 'all'}`
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// 重算路由：使用独立路由模块（减少主文件体积）
const { router: recomputeRouter, setDbPool } = require('./src/routes/recompute.cjs');
setDbPool(dbPool);
app.use('/api/recompute', recomputeRouter);

// PPH重算路由：PPH平均值重算功能
const pphRecomputeRouter = require('./src/routes/pph-recompute.cjs');
app.use('/api/pph', pphRecomputeRouter);

// 报表路由：PPH汇总表和异常汇总表
const { router: reportsRouter, setDbPool: setReportsDbPool } = require('./src/routes/reports.cjs');
setReportsDbPool(dbPool);
app.use('/api/reports', reportsRouter);

// 获取PPH目标值
app.get('/api/targets/pph', async (req, res) => {
  const { material_code, line_name, record_date, whole_machine } = req.query;

  if (!material_code || !line_name || !record_date) {
    res.status(400).json({ error: '缺少必要参数' });
    return;
  }

  try {
    // 目标值查询策略：
    // 1) 优先使用 <= 记录日期 的最近一条记录（生效区间向后延续）
    // 2) 如果没有，则使用 >= 记录日期 的最早一条记录（向前寻找最近配置）
    const queryPrev = `SELECT * FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date <= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date DESC
                       LIMIT 1`;
    const queryNext = `SELECT * FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date >= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date ASC
                       LIMIT 1`;

    const params = [material_code, line_name, record_date];
    const targetRowPrevResults = await dbPool.query(queryPrev, params);
    const targetRowPrev = targetRowPrevResults[0] || null;

    let useRow = targetRowPrev;
    if (!useRow) {
      const targetRowNextResults = await dbPool.query(queryNext, params);
      useRow = targetRowNextResults[0] || null;
    }

    // 查询预估值
    const estimateResults = await dbPool.query(
      "SELECT estimate_pph FROM pph_estimates WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)",
      [material_code, line_name]
    );
    const estimateRow = estimateResults[0] || null;

    const isWholeMachine = String(whole_machine) === 'true';
    let targetPPH = null;
    if (useRow) {
      targetPPH = isWholeMachine ? useRow.whole_machine_target : useRow.motor_target;
    }
    const estimatePPH = estimateRow ? estimateRow.estimate_pph : null;

    res.json({
              material_code,
      material_code,
      line_name,
      record_date,
      whole_machine: isWholeMachine,
      target_pph: targetPPH,
      estimate_pph: estimatePPH,
      target_record_date: useRow ? useRow.record_date : null,
      found: !!useRow
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 获取产品信息
app.get('/api/products/order/:order_number', async (req, res) => {
  const { order_number } = req.params;
  const materialCode = order_number.substring(0, 4); // 简单解析

  try {
    const results = await dbPool.query("SELECT * FROM products WHERE material_code = ?", [materialCode]);
    const row = results[0];
    if (!row) {
      res.status(404).json({ error: '产品不存在' });
    } else {
      res.json({
        order_number,
        material_code: row.material_code,
        product_name: row.product_name,
        product_series: row.product_series
      });
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// 获取PPH目标值
app.get('/api/targets/:material_code/:line', (req, res) => {
  const { material_code, line } = req.params;
  const { date } = req.query;

  db.get(
    `SELECT target_pph FROM pph_targets
     WHERE material_code = ? AND line_name = ? AND effective_date <= ?
       AND (is_active = 1 OR is_active IS NULL)
     ORDER BY effective_date DESC LIMIT 1`,
    [material_code, line, date],
    (err, row) => {
      if (err) {
        res.status(500).json({ error: err.message });
      } else {
        res.json({
          material_code,
          line_name: line,
          date,
          target_pph: row ? row.target_pph : null,
          found: !!row
        });
      }
    }
  );
});

// 获取表结构信息
app.get('/api/debug/table-info/:table', async (req, res) => {
  try {
    const { table } = req.params;
    const info = await dbPool.query(`PRAGMA table_info(${table})`);
    res.json({ success: true, data: info });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
});

// ==================== pph_records 表结构自检/迁移（仅该表） ====================
async function getTableColumns(table) {
  try {
    const info = await dbPool.query(`PRAGMA table_info(${table})`);
    const cols = new Set();
    (info || []).forEach(r => r && r.name && cols.add(String(r.name)));
    return cols;
  } catch (e) {
    return new Set();
  }
}

async function tableExists(table) {
  const rows = await dbPool.query(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, [table]);
  return Array.isArray(rows) && rows.length > 0;
}

async function addColumn(table, name, type) {
  try {
    await dbPool.query(`ALTER TABLE ${table} ADD COLUMN ${name} ${type || ''}`.trim());
    return true;
  } catch (e) {
    console.warn(`添加列失败: ${table}.${name}:`, e && e.message);
    return false;
  }
}

// 通用列存在性检查（用于构造兼容性SQL，避免“no such column”）
async function columnExists(table, column) {
  try {
    const rows = await dbPool.query(`PRAGMA table_info(${table})`);
    return Array.isArray(rows) && rows.some(r => String(r.name).toLowerCase() === String(column).toLowerCase());
  } catch (e) {
    console.warn(`检测列失败: ${table}.${column}:`, e && e.message);
    return false;
  }
}

function nowStamp() {
  const d = new Date();
  const pad = n => String(n).padStart(2, '0');
  return `${d.getFullYear()}${pad(d.getMonth()+1)}${pad(d.getDate())}${pad(d.getHours())}${pad(d.getMinutes())}${pad(d.getSeconds())}`;
}

const PPH_RECORDS_CREATE_SQL = `
CREATE TABLE IF NOT EXISTS pph_records (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  record_date DATE NOT NULL,
  line_name VARCHAR(50) NOT NULL,
  order_number VARCHAR(100) NOT NULL,
  material_code VARCHAR(50) NOT NULL,
  product_name VARCHAR(200) NOT NULL,
  product_series VARCHAR(100),
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  -- 订单相关（可为空）
  order_quantity INTEGER,
  inbound_quantity INTEGER,
  completed_total_quantity INTEGER,
  to_complete_quantity INTEGER,
  target_record_date DATE,
  -- 数量与人员
  completed_quantity INTEGER,
  online_staff INTEGER DEFAULT 0,
  offline_staff INTEGER DEFAULT 0,
  total_staff INTEGER GENERATED ALWAYS AS ((COALESCE(online_staff,0) + COALESCE(offline_staff,0))) STORED,
  -- 质量与损失
  defect_quantity INTEGER DEFAULT 0,
  rework_quantity INTEGER DEFAULT 0,
  scrap_quantity INTEGER DEFAULT 0,
  abnormal_loss_minutes DECIMAL(8,2) DEFAULT 0,
  changeover_minutes DECIMAL(8,2) DEFAULT 0,
  break_minutes DECIMAL(8,2) DEFAULT 0,
  maintenance_time DECIMAL(8,2) DEFAULT 0,
  net_working_minutes DECIMAL(8,2) DEFAULT 0,
  belt_speed DECIMAL(8,2),
  -- 计算字段（可选直存）
  calculated_pph DECIMAL(8,2),
  target_pph DECIMAL(8,2),
  estimate_pph DECIMAL(8,2),
  efficiency_rate DECIMAL(8,2),
  productivity_index DECIMAL(8,2),
  time_utilization_rate DECIMAL(8,2),
  quality_rate DECIMAL(8,2),
  defect_rate DECIMAL(8,2),
  total_person_hours DECIMAL(8,2),
  effective_person_hours DECIMAL(8,2),
  person_utilization_rate DECIMAL(8,2),
  actual_takt_seconds DECIMAL(8,2),
  -- 生产类型标记（1=整机生产，0=整机+电机生产）
  is_whole_machine INTEGER,
  -- 异常信息
  abnormality_category VARCHAR(100),
  abnormality_description TEXT,
  downtime_reason VARCHAR(200),
  -- 系统
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(50) DEFAULT 'system',
  sync_status VARCHAR(20) DEFAULT 'synced'
);
`;

// 删除有问题的 rebuildPPHRecordsTable 函数，统一使用连接池的硬重建
// 原函数使用旧的 db 连接并创建备份表，容易导致视图引用问题

async function hardRebuildPPHRecords() {
  try {
    await dbPool.query('BEGIN IMMEDIATE TRANSACTION');
    await dbPool.query('DROP TABLE IF EXISTS pph_records');
    await dbPool.query(PPH_RECORDS_CREATE_SQL);
    // 唯一约束：A1口径（record_date,line_name,material_code,start_time,end_time）
    await dbPool.query("CREATE UNIQUE INDEX IF NOT EXISTS uq_pph_records_a1 ON pph_records(record_date, line_name, material_code, start_time, end_time)");
    await dbPool.query('COMMIT');
    return { success: true, rebuilt: true };
  } catch (e) {
    try { await dbPool.query('ROLLBACK'); } catch {}
    return { success: false, error: e.message };
  }
}

async function ensurePPHRecordsSchema2(options = {}) {
  const exists = await tableExists('pph_records');
  if (!exists) {
    try {
      await dbPool.query(PPH_RECORDS_CREATE_SQL);
      return { created: true };
    } catch (e) {
      return { created: false, error: e.message };
    }
  }

  const cols = await getTableColumns('pph_records');
  // 判断是否“明显旧表”：缺少核心字段数量较多时认为旧表
  const critical = ['quantity','total_people','abnormal_loss_minutes','break_minutes','changeover_minutes','maintenance_time','net_working_minutes'];
  const missingCritical = critical.filter(c => !cols.has(c));
  if (missingCritical.length >= 3) {
    // 仅重建该表（使用连接池的硬重建）
    return await hardRebuildPPHRecords();
  }

  // 温和补列：只添加缺失但常用的列，避免再次报错
  const addSet = [
    ['calculated_pph','DECIMAL(8,2)'],
    ['target_pph','DECIMAL(8,2)'],
    ['estimate_pph','DECIMAL(8,2)'],
    ['efficiency_rate','DECIMAL(8,2)'],
    ['product_series','VARCHAR(100)'],
    ['downtime_reason','VARCHAR(200)'],
    ['order_quantity','INTEGER'],
    ['inbound_quantity','INTEGER'],
    ['completed_total_quantity','INTEGER'],
    ['to_complete_quantity','INTEGER'],
    ['target_record_date','DATE'],
    ['belt_speed','DECIMAL(8,2)'],
    ['working_people','INTEGER'],
    ['offline_staff','INTEGER'],
    ['total_people','INTEGER'],
    ['net_working_minutes','DECIMAL(8,2)'],
    ['actual_takt_seconds','DECIMAL(8,2)'],
    ['is_standard_operating','INTEGER'],
    ['is_non_main_product','INTEGER'],
    ['is_whole_machine','INTEGER']
  ];

  const added = [];
  for (const [name, type] of addSet) {
    if (!cols.has(name)) {
      const ok = await addColumn('pph_records', name, type);
      if (ok) added.push(name);
    }
  }
  // 确保唯一索引（A1口径）存在，以支持 UPSERT
  try {
    await dbPool.query("CREATE UNIQUE INDEX IF NOT EXISTS uq_pph_records_a1 ON pph_records(record_date, line_name, material_code, start_time, end_time)");
  } catch (e) {
    console.warn('创建唯一索引失败(忽略):', e.message);
  }
  return { created: false, rebuilt: false, added };
}

// 兼容别名：历史代码引用 ensureTableConsistencyGently，这里转调到 ensurePPHRecordsSchema2
async function ensureTableConsistencyGently() {
  try {
    const res = await ensurePPHRecordsSchema2();
    return res;
  } catch (e) {
    return { created: false, rebuilt: false, error: e.message };
  }
}



// 条件批量删除 pph_records（仅在管理开关启用时允许）
app.post('/api/admin/pph-records/bulk-delete', express.json(), async (req, res) => {
  // 安全保护
  if (process.env.ENABLE_ADMIN_DELETE !== '1') {
    return res.status(403).json({ success:false, error:'admin delete disabled by config' });
  }
  try{
    const filter = req.body || {};
    const { buildWhereClauseForRecompute } = require('/backend/recompute/jobs.cjs');
    const { whereSql, params } = buildWhereClauseForRecompute(filter || {});
    const sql = `DELETE FROM pph_records ${whereSql}`;
    const result = await dbPool.query(sql, params);
    res.json({ success:true, deleted: result && result.changes || 0 });
  }catch(e){
    res.status(500).json({ success:false, error: e.message || String(e) });
  }
});

// 清理历史重算任务（仅在管理开关启用时允许）
app.post('/api/admin/recompute/jobs/purge', express.json(), async (req, res) => {
  // 临时启用管理员删除功能用于清理任务记录
  const enableAdminDelete = process.env.ENABLE_ADMIN_DELETE === '1' || true; // 临时启用
  if (!enableAdminDelete) {
    return res.status(403).json({ success:false, error:'admin delete disabled by config' });
  }
  try{
    const { older_than_days = 0, statuses } = req.body || {};
    const allowStatuses = Array.isArray(statuses) && statuses.length ? statuses : ['completed','failed','cancelled'];

    // 验证状态参数，确保只包含允许的状态
    const validStatuses = ['completed', 'failed', 'cancelled', 'running', 'queued'];
    const filteredStatuses = allowStatuses.filter(status => validStatuses.includes(status));

    if (filteredStatuses.length === 0) {
      return res.status(400).json({ success: false, error: '没有有效的状态参数' });
    }

    // 如果包含运行中状态，记录警告日志
    if (filteredStatuses.includes('running')) {
      console.warn('警告：正在删除运行中的任务记录，这可能导致数据不一致');
    }

    const marks = filteredStatuses.map(()=>'?').join(',');
    const params = [...filteredStatuses];
    let where = `status IN (${marks})`;
    if (older_than_days && Number(older_than_days) > 0){
      where += ` AND datetime(created_at) <= datetime('now', ?)`;
      params.push(`-${parseInt(older_than_days,10)} days`);
    }
    const sql = `DELETE FROM recompute_jobs WHERE ${where}`;
    console.log('执行清除任务SQL:', sql, '参数:', params);
    const result = await dbPool.query(sql, params);
    const deletedCount = result && result.changes || 0;
    console.log(`成功清除 ${deletedCount} 条任务记录`);
    res.json({ success:true, deleted: deletedCount });
  }catch(e){
    console.error('清除任务记录失败:', e);
    res.status(500).json({ success:false, error: e.message || String(e) });
  }
});

// 手动触发：仅针对 pph_records 表的自检/迁移
app.post('/api/admin/pph-records/ensure-schema', async (req, res) => {
  // 安全保护：仅在设置 ENABLE_ADMIN_REBUILD=1 时放行
  if (process.env.ENABLE_ADMIN_REBUILD !== '1') {
    return res.status(403).json({ success:false, error:'admin action disabled by config' });
  }
  try {
    const result = await ensurePPHRecordsSchema2();
    res.json({ success: true, result });
  } catch (e) {
    res.status(500).json({ success: false, error: e.message });
  }
});

// 硬重建：仅重建 pph_records（不备份、不迁移，彻底清空）
app.post('/api/admin/pph-records/rebuild-hard', async (req, res) => {
  // 安全保护：仅在设置 ENABLE_ADMIN_REBUILD=1 时放行
  if (process.env.ENABLE_ADMIN_REBUILD !== '1') {
    return res.status(403).json({ success:false, error:'admin action disabled by config' });
  }
  try {
    await dbPool.query('BEGIN IMMEDIATE TRANSACTION');
    await dbPool.query('DROP TABLE IF EXISTS pph_records');
    await dbPool.query(PPH_RECORDS_CREATE_SQL);
    await dbPool.query('COMMIT');
    res.json({ success: true, rebuilt: true });
  } catch (e) {
    try { await dbPool.query('ROLLBACK'); } catch {}
    res.status(500).json({ success: false, error: e.message });
  }
});

// 获取PPH记录列表
app.get('/api/records', async (req, res) => {
  try {
  const { line_name, material_code, record_date, start_date, end_date, order_number, product_name, efficiency_operator, efficiency_value, abnormality_category } = req.query;
  // 兼容多选：line_names, material_codes 支持重复参数或逗号分隔
  const parseMulti = (raw) => {
    try {
      if (Array.isArray(raw)) return raw.flatMap(v => String(v).split(',').map(s => s.trim()).filter(Boolean));
      if (typeof raw === 'string') return raw.split(',').map(s => s.trim()).filter(Boolean);
      return [];
    } catch (_) { return []; }
  };
  const line_names = parseMulti(req.query.line_names);
  const material_codes = parseMulti(req.query.material_codes).map(s => s.toUpperCase());

  // 兼容：page/limit 优先于 offset
  const limit = parseInt(req.query.limit ?? '50');
  const page = parseInt(req.query.page ?? '1');
  const offset = req.query.offset !== undefined ? parseInt(req.query.offset) : Math.max((page - 1), 0) * limit;

    // 检查表是否存在
    const tableExists = await dbPool.query(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='pph_records'"
    );

    if (!tableExists || tableExists.length === 0) {
      return res.json({
        success: true,
        data: [],
        total: 0,
        message: 'PPH记录表不存在，返回空数据'
      });
    }

    // 统计总数（用于分页返回）
    let whereSql = ' WHERE 1=1';
    const whereParams = [];

    if (line_name) {
      whereSql += ' AND TRIM(line_name) = TRIM(?)';
      whereParams.push(line_name);
    }
    // 批量生产线
    if (Array.isArray(line_names) && line_names.length) {
      const marks = line_names.map(()=>' ?').join(',').replace(/\s/g,'');
      whereSql += ` AND TRIM(line_name) IN (${marks})`;
      whereParams.push(...line_names);
    }

    if (material_code) {
      whereSql += ' AND UPPER(material_code) = UPPER(?)';
      whereParams.push(material_code);
    }
    // 批量物料编码（统一大写比较）
    if (Array.isArray(material_codes) && material_codes.length) {
      const marks = material_codes.map(()=>' ?').join(',').replace(/\s/g,'');
      whereSql += ` AND UPPER(material_code) IN (${marks})`;
      whereParams.push(...material_codes);
    }

    if (order_number) {
      whereSql += ' AND order_number LIKE ?';
      whereParams.push(`%${order_number}%`);
    }

    if (product_name) {
      whereSql += ' AND product_name LIKE ?';
      whereParams.push(`%${product_name}%`);
    }

    if (abnormality_category) {
      whereSql += ' AND abnormality_category = ?';
      whereParams.push(abnormality_category);
    }

    // 达成率条件查询
    if (efficiency_operator && efficiency_value !== undefined && efficiency_value !== null) {
      const effValue = parseFloat(efficiency_value);
      if (!isNaN(effValue)) {
        switch (efficiency_operator) {
          case 'gt':
            whereSql += ' AND efficiency_rate > ?';
            whereParams.push(effValue);
            break;
          case 'gte':
            whereSql += ' AND efficiency_rate >= ?';
            whereParams.push(effValue);
            break;
          case 'eq':
            whereSql += ' AND efficiency_rate = ?';
            whereParams.push(effValue);
            break;
          case 'lte':
            whereSql += ' AND efficiency_rate <= ?';
            whereParams.push(effValue);
            break;
          case 'lt':
            whereSql += ' AND efficiency_rate < ?';
            whereParams.push(effValue);
            break;
        }
      }
    }

    if (record_date) {
      whereSql += ' AND record_date = ?';
      whereParams.push(record_date);
    } else {
      // 可选的日期区间过滤（闭区间）
      if (start_date) {
        whereSql += ' AND record_date >= ?';
        whereParams.push(start_date);
      }
      if (end_date) {
        whereSql += ' AND record_date <= ?';
        whereParams.push(end_date);
      }
    }

    console.log('🔍 查询条件SQL:', whereSql);
    console.log('🔍 查询参数:', whereParams);

    const countRow = await dbPool.query(`SELECT COUNT(1) as cnt FROM pph_records${whereSql}`, whereParams);
    const total = Array.isArray(countRow) && countRow[0] ? Number(countRow[0].cnt || 0) : 0;

    console.log('🔍 查询结果总数:', total);

    const sql = `SELECT * FROM pph_records${whereSql} ORDER BY id DESC LIMIT ? OFFSET ?`;
    const records = await dbPool.query(sql, [...whereParams, limit, offset]);

    // 直接使用 is_main_product 字段（1=主打，0=非主打，null=未设置）
    const mapped = (records || []).map(r => ({
      ...r,
      is_main_product: (r.is_main_product === 1 ? true : (r.is_main_product === 0 ? false : null))
    }));

    // 计算基于查询条件的全量统计数据
    const statsQuery = `
      SELECT
        COUNT(*) as total_records,
        AVG(CASE WHEN calculated_pph > 0 THEN calculated_pph ELSE NULL END) as average_pph,
        AVG(CASE WHEN efficiency_rate IS NOT NULL AND target_pph > 0 THEN efficiency_rate ELSE NULL END) as average_efficiency,
        COUNT(CASE WHEN efficiency_rate >= 90 AND target_pph > 0 THEN 1 ELSE NULL END) * 100.0 /
        NULLIF(COUNT(CASE WHEN target_pph > 0 THEN 1 ELSE NULL END), 0) as achievement_rate
      FROM pph_records${whereSql}
    `;

    const statsResult = await dbPool.query(statsQuery, whereParams);
    const stats = statsResult?.[0] || {};

    // 为了兼容历史前端，除了 pagination.total 外，额外返回顶层 total 字段
    res.json({
      success: true,
      data: mapped,
      total: total,
      pagination: {
        page: page,
        limit: limit,
        total: total
      },
      statistics: {
        totalRecords: stats.total_records || 0,
        averagePPH: Math.round((stats.average_pph || 0) * 10) / 10,
        averageEfficiency: Math.round((stats.average_efficiency || 0) * 10) / 10,
        achievementRate: Math.round((stats.achievement_rate || 0) * 10) / 10
      }
    });
  } catch (error) {
    console.error('获取PPH记录列表失败:', error);
    res.status(500).json({
      success: false,
      error: '获取PPH记录列表失败',
      details: error.message
    });
  }
});

// Excel导出API - PPH记录 (必须在 :id 路由之前)
app.get('/api/records/export', async (req, res) => {
  try {
    console.log('📊 开始导出PPH记录...');

    // 解析查询参数
    const {
      start_date,
      end_date,
      line_name,
      order_number,
      material_code,
      product_name,
      efficiency_operator,
      efficiency_value,
      fields = 'record_date,line_name,order_number,material_code,product_name,completed_quantity,online_staff,offline_staff,total_staff,calculated_pph,target_pph,efficiency_rate'
    } = req.query;

    // 字段映射配置（补齐中文映射，不变更字段口径）
    const fieldMapping = {
      record_date: '记录日期',
      target_record_date: '目标记录日期',
      line_name: '生产线',
      order_number: '订单号',
      material_code: '物料编码',
      product_name: '产品名称',
      product_series: '产品系列',
      quantity: '完成数量',
      working_people: '线上人数',
      offline_staff: '线外人数',
      total_people: '总人数',
      calculated_pph: '实际PPH',
      target_pph: '目标PPH',
      estimate_pph: '预估PPH',
      efficiency_rate: '达成率(%)',
      abnormality_category: '异常类别',
      abnormality_description: '异常描述',
      abnormality_closed: '异常关闭',
      start_time: '开始时间',
      end_time: '结束时间',
      break_minutes: '休息时间(分)',
      abnormal_loss_minutes: '异常损失(分)',
      changeover_minutes: '转产时间(分)',
      maintenance_time: '维修时间(分)',
      net_working_minutes: '净工作时间(分)',
      actual_takt_seconds: '实际节拍(秒)',
      target_takt_time: '节拍',
      target_motor_takt_time: '整机节拍',
      belt_speed: '皮带速度(秒/格)',
      defect_quantity: '不良数量',
      rework_quantity: '返工数量',
      scrap_quantity: '报废数量',
      downtime_reason: '停机原因',
      // 新增补齐
      order_quantity: '订单数量',
      inbound_quantity: '已入库数量',
      completed_total_quantity: '累计完工数量',
      to_complete_quantity: '待完工数量',
      is_standard_operating: '符合标准作业',
      is_main_product: '主打产品',
      is_non_main_product: '主打产品', // 兼容旧字段名，但显示为主打产品
      is_whole_machine: '生产类型',
      demand_category: '需求分类',
      customer_number: '顾客号',
      created_at: '创建时间',
      updated_at: '更新时间',
      created_by: '创建人',
      sync_status: '同步状态',
      // 兼容旧字段名
      average_pph: 'PPH平均值'
    };

    // 字段名映射：将前端可能发送的错误字段名映射为正确的数据库字段名
    const fieldNameMapping = {
      'exception_loss_time': 'abnormal_loss_minutes',
      'break_time': 'break_minutes',
      'transfer_time': 'changeover_minutes'
    };

    // 解析要导出的字段并修正字段名
    const exportFieldsRaw = fields.split(',')
      .map(f => (f||'').trim())
      .filter(f => f);

    // 获取表结构，过滤掉不存在的列，并对未知字段回退原名（不改口径）
    const tableCols = await dbPool.query('PRAGMA table_info(pph_records)');
    const existing = new Set((tableCols||[]).map(r => String(r.name)));

    const exportFields = exportFieldsRaw
      .map(name => fieldNameMapping[name] || name)
      .filter(name => existing.has(name));

    // 构建SQL查询 - 只选择需要的字段
    let query = `
      SELECT ${exportFields.join(', ')}
      FROM pph_records
      WHERE 1=1
    `;

    const params = [];

    // 添加查询条件
    if (start_date) {
      query += ` AND record_date >= ?`;
      params.push(start_date);
    }

    if (end_date) {
      query += ` AND record_date <= ?`;
      params.push(end_date);
    }

    if (line_name) {
      query += ` AND TRIM(line_name) = TRIM(?)`;
      params.push(line_name);
    }
    // 批量生产线（导出）
    const parseMulti = (raw) => {
      try {
        if (Array.isArray(raw)) return raw.flatMap(v => String(v).split(',').map(s => s.trim()).filter(Boolean));
        if (typeof raw === 'string') return raw.split(',').map(s => s.trim()).filter(Boolean);
        return [];
      } catch (_) { return []; }
    };
    const line_names = parseMulti(req.query.line_names);
    if (line_names.length) {
      const marks = line_names.map(()=>'?').join(',');
      query += ` AND TRIM(line_name) IN (${marks})`;
      params.push(...line_names);
    }

    if (order_number) {
      query += ` AND order_number LIKE ?`;
      params.push(`%${order_number}%`);
    }

    if (material_code) {
      query += ` AND UPPER(material_code) = UPPER(?)`;
      params.push(material_code);
    }
    // 批量物料
    const material_codes = parseMulti(req.query.material_codes).map(s=>s.toUpperCase());
    if (material_codes.length) {
      const marks = material_codes.map(()=>'?').join(',');
      query += ` AND UPPER(material_code) IN (${marks})`;
      params.push(...material_codes);
    }

    if (product_name) {
      query += ` AND product_name LIKE ?`;
      params.push(`%${product_name}%`);
    }

    // 达成率条件
    if (efficiency_operator && efficiency_value) {
      const operators = {
        'gt': '>',
        'gte': '>=',
        'eq': '=',
        'lte': '<=',
        'lt': '<'
      };

      if (operators[efficiency_operator]) {
        query += ` AND efficiency_rate ${operators[efficiency_operator]} ?`;
        params.push(parseFloat(efficiency_value));
      }
    }

    query += ' ORDER BY record_date DESC, line_name ASC';

    console.log('🔍 执行查询:', query);
    console.log('📝 查询参数:', params);

    // 执行查询
    const records = await dbPool.query(query, params);

    console.log(`📋 查询到 ${records.length} 条记录`);

    // 创建Excel工作簿
    const XLSX = require('xlsx');
    const workbook = XLSX.utils.book_new();

    // 准备数据
    const headers = exportFields.map(field => fieldMapping[field] || field);
    const data = [headers];

    // 添加数据行
    records.forEach(record => {
      const rowData = exportFields.map(field => {
        let value = record[field];

        // 格式化特殊字段
        if (field === 'record_date' || field === 'target_record_date') {
          value = value || '-';
        } else if (field === 'abnormality_closed') {
          value = value === 1 ? '是' : (value === 0 ? '否' : '-');
        } else if (field === 'is_standard_operating') {
          value = value === 1 ? '是' : (value === 0 ? '否' : '-');
        } else if (field === 'is_main_product') {
          // 主打产品字段：is_main_product(1=主打，0=非主打)
          value = value === 1 ? '是' : (value === 0 ? '否' : '-');
        } else if (field === 'is_whole_machine') {
          value = value === 1 ? '整机生产' : (value === 0 ? '整机+电机生产' : '-');
        } else if (field === 'efficiency_rate' && value !== null && value !== undefined) {
          value = `${parseFloat(value).toFixed(1)}%`;
        } else if (['calculated_pph', 'target_pph', 'estimate_pph', 'average_pph'].includes(field) && value !== null && value !== undefined) {
          value = parseFloat(value).toFixed(2);
        }

        // 格式化特殊字段
        if (field === 'record_date' && value) {
          value = new Date(value).toLocaleDateString('zh-CN');
        } else if (field === 'efficiency_rate' && value !== null) {
          value = `${parseFloat(value).toFixed(1)}%`;
        } else if (['calculated_pph', 'target_pph'].includes(field) && value !== null) {
          value = parseFloat(value).toFixed(2);
        } else if (field === 'is_standard_operating') {
          value = (record.is_standard_operating === 1) ? '是' : '否';
        } else if (field === 'is_non_main_product') {
          // 转为“主打产品”的是/否显示：is_non_main_product=0→主打（是）；1→非主打（否）
          value = (record.is_non_main_product === 0) ? '是' : '否';
        } else if (field === 'is_whole_machine') {
          value = (record.is_whole_machine === 1) ? '整机生产' : '整机+电机生产';
        }

        return (value ?? '') === '' ? '' : value;
      });

      data.push(rowData);
    });

    // 创建工作表
    const worksheet = XLSX.utils.aoa_to_sheet(data);

    // 设置列宽
    const colWidths = headers.map((header, index) => {
      let maxLength = header.length;

      data.slice(1).forEach(row => {
        const cellLength = row[index] ? row[index].toString().length : 0;
        if (cellLength > maxLength) {
          maxLength = cellLength;
        }
      });

      return { wch: Math.min(Math.max(maxLength + 2, 10), 30) };
    });

    worksheet['!cols'] = colWidths;

    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH报表');

    // 设置响应头
    const filename = `PPH报表_${new Date().toISOString().slice(0, 10)}.xlsx`;
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`);

    // 输出Excel文件
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    res.end(buffer);

    console.log('✅ PPH报表导出完成');

  } catch (error) {
    console.error('❌ 导出PPH报表失败:', error);
    res.status(500).json({
      success: false,
      message: '导出失败',
      error: error.message
    });
  }
});

// 获取单个PPH记录详情
app.get('/api/records/:id', async (req, res) => {
  try {
    const { id } = req.params;
    if (!id || isNaN(Number(id))) {
      return res.status(400).json({ success: false, error: '无效的记录ID' });
    }

    const rows = await dbPool.query('SELECT * FROM pph_records WHERE id = ?', [Number(id)]);
    if (!rows || rows.length === 0) {
      return res.status(404).json({ success: false, error: '记录不存在' });
    }

    const record = rows[0];
    const mapped = {
      ...record,
      is_main_product: (record.is_main_product === 1 ? true : (record.is_main_product === 0 ? false : null))
    };
    return res.json({ success: true, data: mapped });
  } catch (error) {
    console.error('获取PPH记录详情失败:', error);
    res.status(500).json({ success: false, error: '获取PPH记录详情失败', details: error.message });
  }
});

// 保存PPH记录 - 支持所有新字段 - 增强版本
// 先做字段归一化，确保验证通过
function normalizePPHRecordInput(req, res, next) {
  try {
    const raw = req.body || {};
    const online = Number(raw.online_staff);
    const offline = Number(raw.offline_staff);
    const sum = (isNaN(online) ? 0 : online) + (isNaN(offline) ? 0 : offline);

    let totalPeople;
    if (raw.total_people !== undefined && raw.total_people !== null) {
      totalPeople = Number(raw.total_people);
    } else if (sum > 0) {
      totalPeople = sum;
    } else if (raw.total_staff !== undefined && raw.total_staff !== null) {
      totalPeople = Number(raw.total_staff);
    }

    const normalized = {
      ...raw,
      // 别名归一化
      quantity: Number(raw.quantity ?? 0) || 0,
      total_people: totalPeople,
      working_people: (raw.working_people !== undefined && raw.working_people !== null)
        ? Number(raw.working_people)
        : undefined,
      offline_staff: Number(raw.offline_staff ?? 0) || 0,
      abnormal_loss_minutes: Number(raw.abnormal_loss_minutes ?? raw.abnormalMinutes ?? 0) || 0,
      changeover_minutes: Number(raw.changeover_minutes ?? raw.changeoverMinutes ?? 0) || 0,
      break_minutes: Number(raw.break_minutes ?? raw.breakMinutes ?? 0) || 0,
      belt_speed: Number(raw.belt_speed ?? raw.beltSpeed ?? 0) || 0
    };

    req.body = normalized;
    next();
  } catch (e) {
    next(e);
  }
}

// 可选的请求日志中间件（缺失时不阻塞服务启动）
let logRequests = (req, res, next) => next();
try {
  // 尝试加载调试中间件
  // 注意：如果文件不存在则采用空中间件兜底
  // eslint-disable-next-line global-require, import/no-dynamic-require
  const maybeLogger = require('./debug-pph-requests.cjs');
  if (typeof maybeLogger === 'function') {
    logRequests = maybeLogger;
  } else {
    console.warn('[PPH][WARN] debug-pph-requests.cjs 不导出函数，使用空中间件兜底');
  }
} catch (e) {
  console.warn('[PPH][WARN] 可选调试中间件缺失或加载失败，使用空中间件兜底：', e && e.message ? e.message : String(e));
}
// 包一层保护，避免中间件抛错影响主流程
app.use((req, res, next) => {
  try {
    return logRequests(req, res, next);
  } catch (err) {
    console.error('[PPH][ERROR] logRequests 中间件执行失败：', err && err.message ? err.message : String(err));
    return next();
  }
});

app.post('/api/records', normalizePPHRecordInput, enhancedMiddleware.validateInput('pph_record'), async (req, res) => {
  console.log('📥 收到PPH记录创建请求');
  console.log('📋 原始请求数据:', JSON.stringify(req.body, null, 2));

  // 字段归一化与默认值兜底
  const raw = req.body || {};
  // 规范化人数
  const _online = Number(raw.online_staff);
  const _offline = Number(raw.offline_staff);
  const _sum = (isNaN(_online) ? 0 : _online) + (isNaN(_offline) ? 0 : _offline);
  let normalizedTotal;
  if (raw.total_people !== undefined && raw.total_people !== null) {
    normalizedTotal = Number(raw.total_people);
  } else if (!isNaN(_sum) && _sum > 0) {
    normalizedTotal = _sum;
  } else if (raw.total_staff !== undefined && raw.total_staff !== null) {
    normalizedTotal = Number(raw.total_staff);
  } else {
    normalizedTotal = undefined;
  }

  let normalizedWorking;
  if (raw.working_people !== undefined && raw.working_people !== null) {
    normalizedWorking = Number(raw.working_people);
  } else if (!isNaN(_online)) {
    normalizedWorking = _online;
  } else {
    normalizedWorking = undefined;
  }

  const data = {
    ...raw,
    // 别名兼容：线上/线外人员
    total_people: normalizedTotal,
    working_people: normalizedWorking,
    // 损失时间（分钟）
    abnormal_loss_minutes: Number(raw.abnormal_loss_minutes ?? raw.abnormalMinutes ?? 0) || 0,
    changeover_minutes: Number(raw.changeover_minutes ?? raw.changeoverMinutes ?? 0) || 0,
    break_minutes: Number(raw.break_minutes ?? raw.breakMinutes ?? 0) || 0,
    // 皮带速度
    belt_speed: Number(raw.belt_speed ?? raw.beltSpeed ?? 0) || 0,
    // 数量相关
    quantity: Number(raw.quantity ?? 0) || 0,
    // 字段映射：前端字段名 -> 数据库字段名
    is_standard_operating: raw.meets_standard !== undefined ? (raw.meets_standard ? 1 : 0) : null,
    // 统一主打产品字段：前端is_main_product(true=主打) -> 数据库is_main_product(1=主打)
    is_main_product: raw.is_main_product !== undefined ? (raw.is_main_product ? 1 : 0) : null,
    is_whole_machine: raw.whole_machine_production !== undefined ? (raw.whole_machine_production ? 1 : 0) : null
  };

  console.log('🔄 字段映射后的数据:');
  console.log('  - meets_standard:', raw.meets_standard, '→ is_standard_operating:', data.is_standard_operating);
  console.log('  - is_main_product:', raw.is_main_product, '→ is_main_product:', data.is_main_product);
  console.log('  - whole_machine_production:', raw.whole_machine_production, '→ is_whole_machine:', data.is_whole_machine);
  console.log('  - net_working_minutes:', data.net_working_minutes);
  console.log('🔍 字段含义验证:');
  console.log('  - is_standard_operating =', data.is_standard_operating, '→', data.is_standard_operating === 1 ? '是标准作业' : '不是标准作业');
  console.log('  - is_main_product =', data.is_main_product, '→', data.is_main_product === 1 ? '是主打产品' : '不是主打产品');
  console.log('  - is_whole_machine =', data.is_whole_machine, '→', data.is_whole_machine === 1 ? '是整机' : '不是整机');

  // 可选：保存时必要时再从用友API获取一次订单信息（仅填补缺失字段，不覆盖前端已填）
  try {
    const enableRefetch = String(process.env.PPH_SAVE_REFETCH_ORDER || '1') === '1';
    const needRefetch = enableRefetch && data && data.order_number && (
      !data.material_code || !data.product_name || data.order_quantity == null || data.inbound_quantity == null || data.completed_total_quantity == null
    );
    if (needRefetch) {
      const cleaned = cleanOrderNumber(String(data.order_number));
      // 复用生产订单服务，短超时，失败不阻塞
      const listResult = await productionOrderService.getProductionOrders({
        pageIndex: 1,
        pageSize: 1,
        code: cleaned,
        requestTimeoutMs: 15000 // 增加到15秒，提高成功率
      });
      if (listResult && listResult.success) {
        let orders = Array.isArray(listResult.data?.orders) ? listResult.data.orders : [];
        if (orders.length > 0) {
          orders = orders.filter(o => String(o.code || '').toUpperCase() === cleaned);
        }
        if (orders.length > 0) {
          const src = orders[0];
          const orderProducts = src.OrderProduct || src.orderProduct || src.orderproduct || src.orderProducts || src.orderProductList || [];
          const op = Array.isArray(orderProducts) && orderProducts.length > 0 ? orderProducts[0] : {};

          const quantityCand = computeQuantityCandidate(op, src);
          const inboundCand = computeInboundCandidate(op, src);
          const orderQty = firstNonNullNum(src.OrderProduct_quantity, quantityCand);
          const inboundQty = firstNonNullNum(src.OrderProduct_incomingQuantity, inboundCand);
          const completedTotal = firstNonNullNum(src.OrderProduct_completedQuantity, src.completedQuantity, src.finishQty, inboundQty);

          // 仅填补缺失字段，不覆盖已有前端输入
          if (!data.material_code) data.material_code = firstNonEmpty(src.OrderProduct_productCode, op.productCode, op.materialCode, op.inventoryCode, op.productId?.code, op.product?.code, src.productCode, src.inventoryCode, src.materialCode);
          if (!data.product_name) data.product_name = firstNonEmpty(src.OrderProduct_productName, op.productName, op.materialName, op.inventoryName, op.productId?.name, op.product?.name, src.productName, src.inventoryName, src.materialName);
          if (data.order_quantity == null && orderQty != null) data.order_quantity = orderQty;
          if (data.inbound_quantity == null && inboundQty != null) data.inbound_quantity = inboundQty;
          if (data.completed_total_quantity == null && completedTotal != null) data.completed_total_quantity = completedTotal;
        }
      }
    }
  } catch (e) {
    // 拉取失败不阻塞保存，且不写入任何虚假数据
    console.warn('保存前订单信息补全失败(忽略):', e && e.message);
  }

  // 增强的PPH计算
  const startTime = data.start_time.split(':');
  const endTime = data.end_time.split(':');
  const startMinutes = parseInt(startTime[0]) * 60 + parseInt(startTime[1]);
  let endMinutes = parseInt(endTime[0]) * 60 + parseInt(endTime[1]);

  // 处理跨天情况
  if (endMinutes < startMinutes) {
    endMinutes += 24 * 60;
  }

  const totalMinutes = endMinutes - startMinutes;
  const breakMinutes = Number(data.break_minutes) || 0;
  const abnormalMinutes = Number(data.abnormal_loss_minutes) || 0;
  const changeoverMinutes = Number(data.changeover_minutes) || 0;
  const lossMinutes = breakMinutes + abnormalMinutes + changeoverMinutes;

  const netMinutes = totalMinutes - lossMinutes;
  const netHours = netMinutes / 60;

  // 生产数据（使用统一字段标准）
  const quantity = Number(data.quantity) || 0;  // 使用统一的quantity字段
  const totalPeople = Number(data.total_people) || 0;  // 使用统一的total_people字段
  const workingPeople = Number(data.working_people) || 0;  // 使用统一的working_people字段

  // 计算各项指标
  const calculatedPPH = quantity / (netHours * totalPeople);
  console.log('🔍 DEBUG: calculatedPPH =', calculatedPPH, 'quantity =', quantity, 'netHours =', netHours, 'totalPeople =', totalPeople);
  const timeUtilizationRate = (netMinutes / totalMinutes) * 100;
  const totalPersonHours = netHours * totalPeople;
  const effectivePersonHours = netHours * workingPeople;
  const personUtilizationRate = (workingPeople / totalPeople) * 100;
  const productivityIndex = quantity / netHours;
  const actualTaktSeconds = quantity > 0 ? (netMinutes * 60) / quantity : 0;

  // 兼容旧表：若存在 NOT NULL 的 completed_quantity 列，则一并写入，取值与 quantity 相同
  async function hasColumn(table, column) {
    try {
      // 从连接池获取连接来执行PRAGMA查询
      const connection = await dbPool.getConnection();
      const rows = await new Promise((resolve, reject) => {
        connection.all(`PRAGMA table_info(${table})`, [], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
      dbPool.releaseConnection(connection);

      console.log('🔍 DEBUG hasColumn:', table, column, 'rows:', rows);
      const result = Array.isArray(rows) && rows.some(r => r && String(r.name).toLowerCase() === String(column).toLowerCase());
      console.log('🔍 DEBUG hasColumn result:', result);
      return result;
    } catch (e) {
      console.error('检查列存在性失败:', e.message);
      return false;
    }
  }

  const includeOfflineStaff = await hasColumn('pph_records', 'offline_staff');
  // 确认是否存在目标值日期列
  const includeTargetRecordDate = await hasColumn('pph_records', 'target_record_date');
  // 其他可选列（不同部署可能缺失）
  const includeCalculatedPPH = await hasColumn('pph_records', 'calculated_pph');
  console.log('🔍 DEBUG: includeCalculatedPPH =', includeCalculatedPPH);
  const includeProductSeries = await hasColumn('pph_records', 'product_series');
  const includeOrderQuantity = await hasColumn('pph_records', 'order_quantity');
  const includeInboundQuantity = await hasColumn('pph_records', 'inbound_quantity');
  const includeCompletedTotalQuantity = await hasColumn('pph_records', 'completed_total_quantity');
  const includeToCompleteQuantity = await hasColumn('pph_records', 'to_complete_quantity');
  const includeDowntimeReason = await hasColumn('pph_records', 'downtime_reason');
  const includeBeltSpeed = await hasColumn('pph_records', 'belt_speed');
  const includeIsStandardOperating = await hasColumn('pph_records', 'is_standard_operating');
  const includeIsMainProduct = await hasColumn('pph_records', 'is_main_product');
  const includeIsWholeMachine = await hasColumn('pph_records', 'is_whole_machine');

  // 计算命中的目标值记录日期（最近生效规则）
  async function findNearestTargetRecordDate(materialCode, lineName, recDate) {
    console.log('🔍 开始查找最近目标记录日期:', { materialCode, lineName, recDate });
    if (!materialCode || !lineName || !recDate) {
      console.log('❌ 缺少必要参数，返回null');
      return null;
    }
    const queryPrev = `SELECT record_date FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date <= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date DESC
                       LIMIT 1`;
    const queryNext = `SELECT record_date FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date >= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date ASC
                       LIMIT 1`;
    const params = [materialCode, lineName, recDate];
    try {
      console.log('🔍 执行向前查询...');
      const prev = await dbPool.query(queryPrev, params);
      console.log('🔍 向前查询结果:', prev);
      if (prev && prev.length > 0 && prev[0].record_date) {
        console.log('✅ 找到向前匹配的目标日期:', prev[0].record_date);
        return prev[0].record_date;
      }
      console.log('🔍 执行向后查询...');
      const next = await dbPool.query(queryNext, params);
      console.log('🔍 向后查询结果:', next);
      const result = next && next.length > 0 ? next[0].record_date : null;
      console.log('📅 最终目标日期结果:', result);
      return result;
    } catch (e) {
      console.error('❌ 查找目标记录日期失败:', e.message);
      return null;
    }
  }

  let nearestTargetDate = null;
  try {
    nearestTargetDate = await findNearestTargetRecordDate(data.material_code, data.line_name, data.record_date);
  } catch (e) {
    console.error('❌ findNearestTargetRecordDate异常:', e.message);
    nearestTargetDate = null;
  }

  // 🎯 查询目标PPH和预估PPH
  let target_pph = null, estimate_pph = null, efficiency_rate = null;

  console.debug('🔍[debug] 目标值查询条件检查:');
  console.debug('  物料代码:', data.material_code);
  console.debug('  线体名称:', data.line_name);
  console.debug('  记录日期:', data.record_date);
  console.debug('  最近目标日期:', nearestTargetDate);

  if (data.material_code && data.line_name && nearestTargetDate) {
    console.log('✅ 条件满足，开始查询目标值...');
    try {
      const targetQuery = `SELECT
        whole_machine_target, motor_target, takt_time, motor_takt_time
        FROM pph_targets
        WHERE UPPER(material_code) = UPPER(?)
          AND TRIM(line_name) = TRIM(?)
          AND record_date = ?
          AND (is_active = 1 OR is_active IS NULL)
        ORDER BY updated_at DESC
        LIMIT 1`;

      const targetRows = await dbPool.query(targetQuery, [data.material_code, data.line_name, nearestTargetDate]);
      if (targetRows && targetRows.length > 0) {
        const row = targetRows[0];
        // 按生产类型精确选择目标值：整机生产→whole_machine_target；整机+电机→motor_target（不再做兜底）
        const isWhole = data.is_whole_machine === 1 || data.is_whole_machine === true || data.is_whole_machine === '1';
        target_pph = isWhole
          ? (row.whole_machine_target != null ? Number(row.whole_machine_target) : null)
          : (row.motor_target != null ? Number(row.motor_target) : null);

        // 🆕 同时获取节拍字段
        data.target_takt_time = row.takt_time != null ? Number(row.takt_time) : null;
        data.target_motor_takt_time = row.motor_takt_time != null ? Number(row.motor_takt_time) : null;

        console.log('🎯 找到目标PPH(精确选择):', target_pph, '(整机:', row.whole_machine_target, ', 电机:', row.motor_target, ', isWhole:', isWhole, ')');
        console.log('⏱️ 找到节拍时间:', { takt_time: data.target_takt_time, motor_takt_time: data.target_motor_takt_time });
      }

      // 查询预估PPH
      const estimateQuery = `SELECT estimate_pph
        FROM pph_estimates
        WHERE UPPER(material_code) = UPPER(?)
          AND TRIM(line_name) = TRIM(?)
        ORDER BY updated_at DESC
        LIMIT 1`;

      const estimateRows = await dbPool.query(estimateQuery, [data.material_code, data.line_name]);
      if (estimateRows && estimateRows.length > 0) {
        estimate_pph = estimateRows[0].estimate_pph;
        console.log('📈 找到预估PPH:', estimate_pph);
      }

      // 计算达成率
      const baseline = (target_pph != null && target_pph > 0) ? target_pph : ((estimate_pph != null && estimate_pph > 0) ? estimate_pph : null);
      if (baseline && baseline > 0 && calculatedPPH > 0) {
        efficiency_rate = (calculatedPPH / baseline) * 100;
        console.log('📊 计算达成率:', efficiency_rate, '% (基准:', baseline, ', 实际:', calculatedPPH, ')');
      }
    } catch (e) {
      console.warn('查询目标/预估PPH失败(继续保存):', e.message);
    }
  } else {
    console.log('❌ 条件不满足，跳过目标值查询');
    if (!data.material_code) console.log('  - 缺少物料代码');
    if (!data.line_name) console.log('  - 缺少线体名称');
    if (!nearestTargetDate) console.debug('  -[debug] 未找到目标记录日期');
  }

  // 检查目标PPH/节拍相关字段是否存在
  const includeTargetPPH = await hasColumn('pph_records', 'target_pph');
  const includeEstimatePPH = await hasColumn('pph_records', 'estimate_pph');
  const includeEfficiencyRate = await hasColumn('pph_records', 'efficiency_rate');
  const includeAbnormalityClosed = await hasColumn('pph_records', 'abnormality_closed');
  const includeTargetTaktTime = await hasColumn('pph_records', 'target_takt_time');
  const includeTargetMotorTaktTime = await hasColumn('pph_records', 'target_motor_takt_time');

  // PPH平均值字段已移除

  const columns = [
    'record_date','line_name','order_number',
    'start_time','end_time',
    // 订单相关（存在才写入）
    ...(includeOrderQuantity ? ['order_quantity'] : []),
    ...(includeInboundQuantity ? ['inbound_quantity'] : []),
    ...(includeCompletedTotalQuantity ? ['completed_total_quantity'] : []),
    ...(includeToCompleteQuantity ? ['to_complete_quantity'] : []),
    // 命中的目标值记录日期与节拍字段（如列存在则一并写入）
    ...(includeTargetRecordDate ? ['target_record_date'] : []),
    ...(includeTargetTaktTime ? ['target_takt_time'] : []),
    ...(includeTargetMotorTaktTime ? ['target_motor_takt_time'] : []),
    'quantity',  // 使用统一的quantity字段
    ...(includeOfflineStaff ? ['offline_staff'] : []),
    'abnormal_loss_minutes','break_minutes','changeover_minutes',
    'net_working_minutes','actual_takt_seconds',
    'working_people','total_people',
    ...(includeBeltSpeed ? ['belt_speed'] : []),
    'material_code','product_name',
    ...(includeProductSeries ? ['product_series'] : []),
    // 计算字段（存在 calculated_pph 列时才写入）
    ...(includeCalculatedPPH ? ['calculated_pph'] : []),
    // PPH平均值字段已移除
    // 🎯 目标PPH相关字段
    ...(includeTargetPPH ? ['target_pph'] : []),
    ...(includeEstimatePPH ? ['estimate_pph'] : []),
    ...(includeEfficiencyRate ? ['efficiency_rate'] : []),
    'abnormality_category','abnormality_description',
    ...(includeAbnormalityClosed ? ['abnormality_closed'] : []),
    ...(includeDowntimeReason ? ['downtime_reason'] : []),
    ...(includeIsStandardOperating ? ['is_standard_operating'] : []),
    ...(includeIsMainProduct ? ['is_main_product'] : []),
    ...(includeIsWholeMachine ? ['is_whole_machine'] : []),
    'created_by','sync_status'
  ];
  const placeholders = columns.map(() => '?').join(', ');
  const sql = `INSERT INTO pph_records (${columns.join(', ')}) VALUES (${placeholders})`;
  const values = [
    data.record_date, data.line_name, data.order_number,
    data.start_time, data.end_time,
    // 订单相关（存在才对应写入值）
    ...(includeOrderQuantity ? [data.order_quantity ?? null] : []),
    ...(includeInboundQuantity ? [data.inbound_quantity ?? null] : []),
    ...(includeCompletedTotalQuantity ? [data.completed_total_quantity ?? null] : []),
    ...(includeToCompleteQuantity ? [data.to_complete_quantity ?? null] : []),
    ...(includeTargetRecordDate ? [nearestTargetDate] : []),
    ...(includeTargetTaktTime ? [data.target_takt_time ?? null] : []),
    ...(includeTargetMotorTaktTime ? [data.target_motor_takt_time ?? null] : []),
    // 写入统一的quantity字段
    quantity,
    ...(includeOfflineStaff ? [isNaN(_offline) ? 0 : _offline] : []),
    abnormalMinutes, breakMinutes, changeoverMinutes,
    netMinutes, actualTaktSeconds,
    workingPeople, totalPeople,
    ...(includeBeltSpeed ? [data.belt_speed || null] : []),
    data.material_code, data.product_name,
    ...(includeProductSeries ? [data.product_series] : []),
    ...(includeCalculatedPPH ? [Math.round(calculatedPPH * 100) / 100] : []),
    // PPH平均值字段已移除
    // 🎯 目标PPH相关字段值
    ...(includeTargetPPH ? [target_pph] : []),
    ...(includeEstimatePPH ? [estimate_pph] : []),
    ...(includeEfficiencyRate ? [efficiency_rate] : []),
    data.abnormality_category, data.abnormality_description,
    ...(includeAbnormalityClosed ? [data.abnormality_closed !== null ? (data.abnormality_closed ? 1 : 0) : null] : []),
    ...(includeDowntimeReason ? [data.downtime_reason] : []),
    ...(includeIsStandardOperating ? [data.is_standard_operating] : []),
    ...(includeIsMainProduct ? [data.is_main_product] : []),
    ...(includeIsWholeMachine ? [data.is_whole_machine] : []),
    data.created_by || 'system', 'synced'
  ];

  try {
    // 🔒 开始事务
    console.log('🔒 开始数据库事务...');
    await dbPool.query('BEGIN IMMEDIATE TRANSACTION');

    const result = await dbPool.query(sql, values);
    const recordId = result.lastInsertRowid || result.lastID || result.insertId;

    // 🔒 提交事务
    await dbPool.query('COMMIT');
    console.log('✅ PPH记录保存成功，ID:', recordId, '(事务已提交)');

    // 🎯 异步回填目标值和PPH平均值 - 不阻塞响应
    setImmediate(async () => {
      try {
        await fillTargetValues(recordId, data, calculatedPPH);

        // PPH平均值计算已移除
      } catch (fillError) {
        console.warn('⚠️ 异步回填失败:', fillError.message);
      }
    });

    res.json({
      success: true,
      record_id: recordId,
      calculation: {
        calculatedPPH: Math.round(calculatedPPH * 10) / 10,
        workingMinutes: netMinutes,
        totalMinutes: totalMinutes,
        netWorkingHours: Math.round(netHours * 100) / 100,
        lossMinutes: lossMinutes,
        timeUtilizationRate: Math.round(timeUtilizationRate * 100) / 100,
        totalPersonHours: Math.round(totalPersonHours * 100) / 100,
        effectivePersonHours: Math.round(effectivePersonHours * 100) / 100,
        personUtilizationRate: Math.round(personUtilizationRate * 100) / 100,
        productivityIndex: Math.round(productivityIndex * 100) / 100,
        personHourOutput: Math.round(calculatedPPH * 100) / 100,
        overallEfficiency: Math.round((timeUtilizationRate * personUtilizationRate) / 100) / 100
      },
      message: 'PPH记录保存成功'
    });
  } catch (err) {
    // 🔒 回滚事务
    try {
      console.log('❌ 发生错误，回滚事务:', err.message);
      await dbPool.query('ROLLBACK');
      console.log('🔄 事务已回滚');
    } catch (rollbackError) {
      console.error('❌ 回滚失败:', rollbackError.message);
    }

    console.error('❌ PPH记录创建失败:', err.message);
    res.status(500).json({
      success: false,
      error: err.message,
      message: 'PPH记录创建失败，事务已回滚'
    });
  }
});

// 目标值回填函数 - 独立提取减少复杂度
async function fillTargetValues(recordId, data, calculatedPPH) {
  console.debug('🔄[debug] 开始自动回填目标值...');

  if (!data.material_code || !data.line_name) {
    console.log('❌ 缺少物料代码或线体名称，跳过回填');
    return;
  }

  try {
    // 查找最近的目标记录日期
    const queryPrev = `SELECT record_date FROM pph_targets
                       WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                         AND record_date <= ? AND (is_active = 1 OR is_active IS NULL)
                       ORDER BY record_date DESC LIMIT 1`;

    const targetDateRows = await dbPool.query(queryPrev, [data.material_code, data.line_name, data.record_date]);
    const nearestTargetDate = targetDateRows && targetDateRows.length > 0 ? targetDateRows[0].record_date : null;

    if (!nearestTargetDate) {
      console.debug('❌[debug] 未找到匹配的目标记录日期');
      return;
    }

    let target_pph_filled = null, estimate_pph_filled = null, efficiency_rate_filled = null;

    // 查询目标值和节拍时间
    const targetQuery = `SELECT whole_machine_target, motor_target, takt_time, motor_takt_time FROM pph_targets
                         WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                           AND record_date = ? AND (is_active = 1 OR is_active IS NULL)
                         ORDER BY updated_at DESC LIMIT 1`;

    const targetRows = await dbPool.query(targetQuery, [data.material_code, data.line_name, nearestTargetDate]);
    if (targetRows && targetRows.length > 0) {
      const row = targetRows[0];
      // 按生产类型精确选择目标值（不再兜底）
      const isWhole = data.is_whole_machine === 1 || data.is_whole_machine === true || data.is_whole_machine === '1';
      target_pph_filled = isWhole
        ? (row.whole_machine_target != null ? Number(row.whole_machine_target) : null)
        : (row.motor_target != null ? Number(row.motor_target) : null);

      // 🆕 同时填充节拍字段
      data.target_takt_time = row.takt_time != null ? Number(row.takt_time) : null;
      data.target_motor_takt_time = row.motor_takt_time != null ? Number(row.motor_takt_time) : null;

      console.log('🎯 找到目标PPH(精确选择):', target_pph_filled, 'isWhole:', isWhole);
      console.log('⏱️ 填充节拍时间:', { takt_time: data.target_takt_time, motor_takt_time: data.target_motor_takt_time });
    }

    // 查询预估值
    const estimateQuery = `SELECT estimate_pph FROM pph_estimates
                           WHERE UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?)
                           ORDER BY updated_at DESC LIMIT 1`;

    const estimateRows = await dbPool.query(estimateQuery, [data.material_code, data.line_name]);
    if (estimateRows && estimateRows.length > 0) {
      estimate_pph_filled = estimateRows[0].estimate_pph;
      console.log('📈 找到预估PPH:', estimate_pph_filled);
    }

    // 计算达成率
    const baseline = target_pph_filled || estimate_pph_filled;
    if (baseline && baseline > 0 && calculatedPPH > 0) {
      efficiency_rate_filled = (calculatedPPH / baseline) * 100;
      console.log('📊 计算达成率:', efficiency_rate_filled.toFixed(2), '%');
    }

    // 更新记录（使用事务保护）
    try {
        await dbPool.query('BEGIN IMMEDIATE TRANSACTION');

        const updateSql = `UPDATE pph_records SET
                           target_pph = ?, estimate_pph = ?, efficiency_rate = ?,
                           target_record_date = ?,
                           target_takt_time = ?, target_motor_takt_time = ?,
                           updated_at = CURRENT_TIMESTAMP
                           WHERE id = ?`;

        await dbPool.query(updateSql, [
          target_pph_filled, estimate_pph_filled, efficiency_rate_filled,
          nearestTargetDate,
          data.target_takt_time ?? null,
          data.target_motor_takt_time ?? null,
          recordId
        ]);

        await dbPool.query('COMMIT');
        console.log('✅ 目标值回填完成');
      } catch (updateError) {
        try {
          await dbPool.query('ROLLBACK');
          console.log('🔄 目标值回填事务已回滚');
        } catch (rollbackError) {
          console.error('❌ 目标值回填回滚失败:', rollbackError.message);
        }
        throw updateError;
      }
  } catch (error) {
    console.error('❌ 目标值回填过程出错:', error.message);
    throw error;
  }
}

// 根路径
app.get('/', (req, res) => {
  res.json({
    name: 'PPH生产效率系统API',
    version: '1.0.0',
    port: PPH_BACKEND_PORT,
    status: 'running',
    timestamp: new Date().toISOString()
  });
});

// API 索引端点（用于避免文档链接误导）
app.get('/api', (req, res) => {
  try {
    res.json({
      success: true,
      name: 'PPH生产效率系统API',
      version: '1.0.0',
      port: PPH_BACKEND_PORT,
      status: 'running',
      timestamp: new Date().toISOString()
    });
  } catch (e) {
    try { console.error('GET /api 失败:', e && (e.message || e)); } catch(_){}
    res.status(500).json({ success: false, error: e && (e.message || String(e)) });
  }
});

// 调试端点已移除


// 兼容性实现已移除，统一使用 backend/recompute/jobs.cjs 模块



// 兼容性路由已移除

// 重复路由已移除


// 调试端点已移除



// ==================== 生产订单查询API ====================

// 搜索订单（精确匹配） - 使用用友API
// 说明：根据用户需求，移除模糊/尾号建议，仅支持精确 code 查询。
app.get('/api/orders/search', async (req, res) => {
  try {
    const { q: query } = req.query;
    const qRaw = (query || '').toString().trim();
    if (!qRaw) return res.json({ orders: [] });

    const cleaned = cleanOrderNumber(qRaw);
    const timeoutMs = 6000;

    const result = await productionOrderService.getProductionOrders({
      pageIndex: 1,
      pageSize: 10,
      code: cleaned,
      requestTimeoutMs: timeoutMs
    });

    const list = result.success ? (result.data?.orders || []) : [];
    const exact = list.filter(o => String(o.code || '') === cleaned);

    const orders = exact.map(o => ({
      order_number: o.code || '',
      product_name: (o.OrderProduct?.[0]?.productName) || '',
      material_code: (o.OrderProduct?.[0]?.productCode) || '',
      status: String(o.status ?? '')
    }));

    return res.json({ orders });
  } catch (error) {
    console.error('搜索订单失败:', error);
    res.status(500).json({ error: '搜索订单失败', message: error.message || '服务器内部错误' });
  }
});

// 用友订单信息查询API - 基于旧项目的成功实现
app.get('/api/yonyou/order/:orderNumber', async (req, res) => {
  const { orderNumber } = req.params;

  if (!orderNumber) {
    return res.status(400).json({
      success: false,
      message: '订单号不能为空'
    });
  }

  try {
    console.log('🔍 查询用友云订单:', orderNumber);

    // 使用ProductionOrderService获取token和网关
    await productionOrderService.getAccessToken();

    // 第三步：查询生产订单 - 使用正确的API端点
    const orderRequestBody = {
      pageIndex: 1,
      pageSize: 10,
      code: orderNumber, // 使用订单号查询
      isShowProcess: 1,
      isShowMaterial: true,
      isShowByProduct: true,
      isShowActivity: true
    };

    // 调用用友云生产订单查询API
    const yonyouResponse = await axios.post(
      `${productionOrderService.gatewayUrl}/yonbip/mfg/productionorder/list?access_token=${productionOrderService.accessToken}`,
      orderRequestBody,
      {
        headers: {
          'Content-Type': 'application/json',
          'Accept': 'application/json'
        },
        timeout: 30000 // 30秒超时
      }
    );

    if (!yonyouResponse.data || yonyouResponse.data.code !== '200') {
      console.error('❌ 用友云API返回错误:', yonyouResponse.data);
      return res.status(404).json({
        success: false,
        message: yonyouResponse.data?.message || '在用友云系统中未找到该生产订单',
        error: 'ORDER_NOT_FOUND_IN_YONYOU'
      });
    }

    // 检查是否有数据返回
    const recordList = yonyouResponse.data.data?.recordList || [];
    if (!recordList || recordList.length === 0) {
      return res.status(404).json({
        success: false,
        message: `订单不存在: ${orderNumber}`,
        error: 'ORDER_NOT_FOUND'
      });
    }

    // 获取第一条订单数据
    const orderInfo = recordList[0];
    const orderProduct = orderInfo.OrderProduct?.[0] || {};

    // 获取料号 - 基于旧项目的字段映射
    const materialCode = orderProduct.productCode || orderInfo.OrderProduct_productCode || '';

    // 处理用友云系统的数据格式并转换为我们需要的格式
    const orderData = {
      order_number: orderNumber,
      material_code: materialCode,
      product_name: orderProduct.productName || orderInfo.OrderProduct_productName || '',
      demand_category: orderInfo.productDefineDts__XS11 ||
                       orderProduct.productDefineDts?.XS11 ||
                       orderInfo.productDefineDts?.XS11 ||
                       orderInfo.defineDts?.XS11 ||
                       '',
      customer_number: orderInfo.customerName || orderInfo.ccusname || '',
      order_quantity: parseFloat(orderProduct.quantity || orderProduct.iQuantity || 0),
      status: orderInfo.status || orderInfo.cStatus || '',
      plan_start_date: orderProduct.startDate || '',
      plan_end_date: orderProduct.finishDate || orderProduct.dPlanDate || '',
      // 额外信息
      org_name: orderInfo.orgName || '',
      department_name: orderInfo.departmentName || '',
      unit_name: orderProduct.unit || orderProduct.stockUnitName || '件'
    };

    res.json({
      success: true,
      data: orderData
    });

  } catch (error) {
    console.error('❌ 查询用友云生产订单异常:', error);

    // 区分不同类型的错误
    if (error.code === 'ECONNABORTED' || error.message.includes('timeout')) {
      return res.status(504).json({
        success: false,
        message: '用友云系统响应超时，请稍后重试',
        error: 'TIMEOUT'
      });
    } else if (error.code === 'ECONNREFUSED' || error.code === 'ENOTFOUND') {
      return res.status(503).json({
        success: false,
        message: '无法连接到用友云系统，请检查网络连接',
        error: 'CONNECTION_FAILED'
      });
    } else {
      return res.status(500).json({
        success: false,
        message: '查询生产订单信息时发生未知错误，请稍后重试',
        error: 'UNKNOWN_ERROR',
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      });
    }
  }
});


// 删除重复的服务器启动代码 - 真正的启动在文件末尾

// PPH预估值API
// 获取所有预估值
app.get('/api/estimates', async (req, res) => {
  try {
    console.log('收到预估值查询请求，查询参数:', req.query);

    // 检查表是否存在
    const tableCheck = await dbPool.query("SELECT name FROM sqlite_master WHERE type='table' AND name='pph_estimates'");

    console.log('pph_estimates表是否存在:', tableCheck && tableCheck.length > 0 ? '是' : '否');

    if (!tableCheck || tableCheck.length === 0) {
      console.log('表不存在，返回空数组');
      res.json([]);
      return;
    }

    // 构建查询条件
    let query = 'SELECT * FROM pph_estimates WHERE 1=1';
    const params = [];

    if (req.query.material_code) {
      query += ' AND material_code LIKE ?';
      params.push(`%${req.query.material_code}%`);
    }

    if (req.query.product_name) {
      query += ' AND product_name LIKE ?';
      params.push(`%${req.query.product_name}%`);
    }

    if (req.query.line_name) {
      query += ' AND line_name = ?';
      params.push(req.query.line_name);
    }

    if (req.query.created_date) {
      query += ' AND DATE(created_at) = ?';
      params.push(req.query.created_date);
    }

    query += ' ORDER BY created_at DESC';

    console.log('执行查询:', query, '参数:', params);

    // 查询预估值数据
    const estimates = await dbPool.query(query, params);

    console.log('查询到预估值数据:', estimates ? estimates.length : 'null', '条');
    if (estimates && estimates.length > 0) {
      console.log('第一条数据:', estimates[0]);
    }

    // 设置正确的字符编码
    res.setHeader('Content-Type', 'application/json; charset=utf-8');
    res.json(estimates || []);
  } catch (error) {
    console.error('获取预估值失败:', error);
    res.status(500).json({ error: '获取预估值失败', details: error.message });
  }
});
// 可复用：生成预估值实现
async function generateEstimatesCore() {
  // 1) 时间窗口
  const now = new Date();
  const end = new Date(now.getFullYear(), now.getMonth(), 0); // 上个月最后一天
  const start = new Date(end.getFullYear(), end.getMonth() - 11, 1); // 往前推11个月的1号
  const fmt = (d) => `${d.getFullYear()}-${String(d.getMonth()+1).padStart(2,'0')}-${String(d.getDate()).padStart(2,'0')}`;
  const windowFrom = fmt(start);
  const windowTo = fmt(end);

  // 2) 拉取窗口内原始记录（用于逐级分组与TOP20）
  const selSql = `
    SELECT id, record_date, line_name, material_code, product_name,
           calculated_pph, updated_at, created_at
    FROM pph_records
    WHERE record_date >= ? AND record_date <= ?
    ORDER BY record_date DESC, COALESCE(updated_at, created_at) DESC, id DESC`;
  const raw = await dbPool.query(selSql, [windowFrom, windowTo]);

  // 3) 一级：按 (record_date, material_code, line_name) 分组，组内取前20条，求“日均PPH”
  const dailyMap = new Map(); // key: date||material||line -> { dailyAvg, product_name, record_date, material_code, line_name }
  for (const r of raw || []) {
    const dateStr = String(r.record_date);
    const mat = String(r.material_code || '').toUpperCase();
    const line = String(r.line_name || '').trim();
    const key = `${dateStr}__${mat}__${line}`;
    if (!dailyMap.has(key)) dailyMap.set(key, { rows: [], record_date: dateStr, material_code: mat, line_name: line, product_name: r.product_name || '' });
    const bucket = dailyMap.get(key);
    if (bucket.rows.length < 20) {
      const pph = r.calculated_pph != null ? Number(r.calculated_pph) : null;
      if (pph != null && !isNaN(pph) && pph > 0) {
        bucket.rows.push(pph);
        // 更新产品名称为最新记录的名称
        if (r.product_name) bucket.product_name = r.product_name;
      }
    }
  }

  const dailyList = [];
  for (const key of dailyMap.keys()) {
    const b = dailyMap.get(key);
    if (b.rows.length > 0) {
      const avg = b.rows.reduce((a, v) => a + v, 0) / b.rows.length;
      dailyList.push({
        record_date: b.record_date,
        material_code: b.material_code,
        line_name: b.line_name,
        product_name: b.product_name || '',
        daily_avg_pph: Math.round(avg * 100) / 100
      });
    }
  }

  // 4) 二级：按 (material_code, line_name) 汇总，按日期倒序取最近20个“日均PPH”，再取平均，得到最终 estimate
  //    注意：这里按 record_date DESC 取最多20天的“日均PPH”
  const byML = new Map();
  for (const d of dailyList.sort((a,b)=> (a.record_date<b.record_date?1:(a.record_date>b.record_date?-1:0)))) {
    const key = `${d.material_code}__${d.line_name}`;
    if (!byML.has(key)) byML.set(key, { list: [], latestName: d.product_name });
    const obj = byML.get(key);
    if (obj.list.length < 20) obj.list.push(d);
    // 最新的产品名
    obj.latestName = d.product_name || obj.latestName;
  }

  const toInsert = [];
  for (const [key, obj] of byML.entries()) {
    if (!obj.list.length) continue;
    const est = obj.list.reduce((acc, it) => acc + Number(it.daily_avg_pph || 0), 0) / obj.list.length;
    const estimate = Math.round(est * 100) / 100;
    const [materialCode, lineName] = key.split('__');
    if (estimate > 0) {
      toInsert.push({ material_code: materialCode, product_name: obj.latestName || '', line_name: lineName, estimate_pph: estimate });
    }
  }

  // 5) 事务写入
  await dbPool.query('BEGIN IMMEDIATE TRANSACTION');
  try {
    await dbPool.query('DELETE FROM pph_estimates');
    for (const row of toInsert) {
      await dbPool.query(
        'INSERT INTO pph_estimates (material_code, product_name, line_name, estimate_pph) VALUES (?, ?, ?, ?)',
        [row.material_code, row.product_name || '', row.line_name, row.estimate_pph]
      );
    }
    await dbPool.query('COMMIT');
  } catch (e) {
    try { await dbPool.query('ROLLBACK'); } catch {}
    throw e;
  }

  return { inserted: toInsert.length, window: { from: windowFrom, to: windowTo }, method: 'daily-top20-average' };
}


/**
 * 正确的预估值计算逻辑
 * 1. 先合并同一天、同产线、同料号的记录（累加完成数量、累加投入工时、求平均人数）
 * 2. 按合并后的完成数量倒序排列，取前20条
 * 3. 计算每个"料号+产线"的平均值
 */
async function generateEstimatesCoreCorrect() {
  console.log('🔧 开始正确的预估值计算...');

  // 1. 获取近一年的数据并按要求分组合并
  const end = new Date();
  const start = new Date(end.getTime() - 365 * 24 * 60 * 60 * 1000);
  const fmt = d => d.toISOString().split('T')[0];
  const windowFrom = fmt(start);
  const windowTo = fmt(end);

  console.log(`📅 数据窗口: ${windowFrom} ~ ${windowTo}`);

  // 2. 按日期+产线+料号分组合并，累加数量和工时，求平均人数
  const mergedQuery = `
    SELECT
      record_date,
      line_name,
      material_code,
      MAX(product_name) as product_name,
      SUM(quantity) as total_quantity,
      SUM(net_working_minutes) as total_minutes,
      AVG(total_people) as avg_people,
      COUNT(*) as record_count
    FROM pph_records
    WHERE record_date >= ? AND record_date <= ?
      AND material_code IS NOT NULL
      AND line_name IS NOT NULL
      AND quantity > 0
      AND net_working_minutes > 0
      AND total_people > 0
    GROUP BY record_date, line_name, material_code
    ORDER BY material_code, line_name, total_quantity DESC
  `;

  const allMergedResults = await dbPool.query(mergedQuery, [windowFrom, windowTo]);
  console.log(`📊 合并后找到 ${allMergedResults.length} 条记录`);

  // 3. 按物料代码+产线分组，每组取前20条，然后计算PPH
  const groupedByMaterialLine = new Map();

  // 先按物料代码+产线分组
  for (const record of allMergedResults) {
    const key = `${record.material_code.toUpperCase()}__${record.line_name.trim()}`;
    if (!groupedByMaterialLine.has(key)) {
      groupedByMaterialLine.set(key, []);
    }
    groupedByMaterialLine.get(key).push(record);
  }

  // 每组取前20条并计算PPH
  const processedRecords = [];
  for (const [groupKey, records] of groupedByMaterialLine.entries()) {
    // 按完成数量倒序排列，取前20条
    const top20Records = records
      .sort((a, b) => b.total_quantity - a.total_quantity)
      .slice(0, 20);

    console.log(`📊 ${groupKey}: ${records.length}条记录，取前${top20Records.length}条`);

    // 计算每条记录的PPH
    for (const r of top20Records) {
      const netHours = r.total_minutes / 60;
      const pph = r.total_quantity / (netHours * r.avg_people);

      if (pph > 0) {
        processedRecords.push({
          record_date: r.record_date,
          material_code: r.material_code.toUpperCase(),
          line_name: r.line_name.trim(),
          product_name: r.product_name || '',
          calculated_pph: Math.round(pph * 100) / 100,
          total_quantity: r.total_quantity,
          total_minutes: r.total_minutes,
          avg_people: Math.round(r.avg_people * 10) / 10,
          record_count: r.record_count
        });
      }
    }
  }

  console.log(`✅ 有效记录数: ${processedRecords.length}`);

  // 4. 按料号+产线分组，计算平均预估值
  const estimateMap = new Map();
  for (const record of processedRecords) {
    const key = `${record.material_code}__${record.line_name}`;
    if (!estimateMap.has(key)) {
      estimateMap.set(key, {
        material_code: record.material_code,
        line_name: record.line_name,
        product_name: record.product_name,
        records: []
      });
    }
    estimateMap.get(key).records.push(record);
    // 更新为最新的产品名称
    if (record.product_name) {
      estimateMap.get(key).product_name = record.product_name;
    }
  }

  const toInsert = [];
  for (const [groupKey, group] of estimateMap.entries()) {
    if (group.records.length === 0) continue;

    // 加权平均计算：按完成数量加权
    let totalWeightedPPH = 0;
    let totalWeight = 0;

    for (const record of group.records) {
      const weight = record.total_quantity; // 使用完成数量作为权重
      totalWeightedPPH += record.calculated_pph * weight;
      totalWeight += weight;
    }

    const estimate = totalWeight > 0
      ? Math.round((totalWeightedPPH / totalWeight) * 100) / 100
      : 0;

    if (estimate > 0) {
      toInsert.push({
        material_code: group.material_code,
        product_name: group.product_name || '',
        line_name: group.line_name,
        estimate_pph: estimate
      });

      console.log(`📈 ${group.material_code} @ ${group.line_name}: ${estimate} PPH (基于${group.records.length}条记录，加权平均)`);
    }
  }

  // 5. 事务写入
  await dbPool.query('BEGIN IMMEDIATE TRANSACTION');
  try {
    await dbPool.query('DELETE FROM pph_estimates');
    console.log('🗑️ 清空现有预估值');

    for (const item of toInsert) {
      await dbPool.query(
        'INSERT INTO pph_estimates (material_code, product_name, line_name, estimate_pph) VALUES (?, ?, ?, ?)',
        [item.material_code, item.product_name, item.line_name, item.estimate_pph]
      );
    }

    await dbPool.query('COMMIT');
    console.log(`✅ 成功写入 ${toInsert.length} 条预估值`);

    return {
      processed_records: processedRecords.length,
      generated_estimates: toInsert.length,
      inserted: toInsert.length, // 前端兼容字段
      window: { from: windowFrom, to: windowTo },
      method: 'weighted-person-hours'
    };
  } catch (error) {
    await dbPool.query('ROLLBACK');
    throw error;
  }
}

// 测试新的预估值计算逻辑（不写入数据库，仅返回计算结果）
app.get('/api/estimates/test-calculation', async (req, res) => {
  try {
    console.log('🧪 测试新的预估值计算逻辑...');

    // 获取测试参数
    const { material_code, line_name, days = 365 } = req.query;

    const end = new Date();
    const start = new Date(end.getTime() - Number(days) * 24 * 60 * 60 * 1000);
    const fmt = d => d.toISOString().split('T')[0];
    const windowFrom = fmt(start);
    const windowTo = fmt(end);

    // 构建查询条件
    let whereClause = 'WHERE record_date >= ? AND record_date <= ?';
    let params = [windowFrom, windowTo];

    if (material_code) {
      whereClause += ' AND UPPER(material_code) = UPPER(?)';
      params.push(material_code);
    }
    if (line_name) {
      whereClause += ' AND TRIM(line_name) = TRIM(?)';
      params.push(line_name);
    }

    // 按日期+产线+料号分组合并
    const mergedQuery = `
      SELECT
        record_date,
        line_name,
        material_code,
        MAX(product_name) as product_name,
        SUM(quantity) as total_quantity,
        SUM(net_working_minutes) as total_minutes,
        AVG(total_people) as avg_people,
        COUNT(*) as record_count
      FROM pph_records
      ${whereClause}
        AND material_code IS NOT NULL
        AND line_name IS NOT NULL
        AND quantity > 0
        AND net_working_minutes > 0
        AND total_people > 0
      GROUP BY record_date, line_name, material_code
      ORDER BY total_quantity DESC
      LIMIT 20
    `;

    const mergedResults = await dbPool.query(mergedQuery, params);

    // 计算每条记录的PPH
    const processedRecords = mergedResults.map(r => {
      const netHours = r.total_minutes / 60;
      const pph = r.total_quantity / (netHours * r.avg_people);
      return {
        record_date: r.record_date,
        material_code: r.material_code,
        line_name: r.line_name,
        product_name: r.product_name,
        total_quantity: r.total_quantity,
        total_minutes: r.total_minutes,
        avg_people: Math.round(r.avg_people * 10) / 10,
        record_count: r.record_count,
        calculated_pph: Math.round(pph * 100) / 100
      };
    }).filter(r => r.calculated_pph > 0);

    // 按料号+产线分组计算预估值
    const estimateMap = new Map();
    for (const record of processedRecords) {
      const key = `${record.material_code}__${record.line_name}`;
      if (!estimateMap.has(key)) {
        estimateMap.set(key, {
          material_code: record.material_code,
          line_name: record.line_name,
          product_name: record.product_name,
          records: []
        });
      }
      estimateMap.get(key).records.push(record);
      if (record.product_name) {
        estimateMap.get(key).product_name = record.product_name;
      }
    }

    const estimates = [];
    for (const group of estimateMap.values()) {
      if (group.records.length === 0) continue;

      const avgPPH = group.records.reduce((sum, r) => sum + r.calculated_pph, 0) / group.records.length;
      const estimate = Math.round(avgPPH * 100) / 100;

      if (estimate > 0) {
        estimates.push({
          material_code: group.material_code,
          product_name: group.product_name || '',
          line_name: group.line_name,
          estimate_pph: estimate,
          base_records: group.records.length,
          calculation_details: group.records
        });
      }
    }

    res.json({
      success: true,
      window: { from: windowFrom, to: windowTo, days: Number(days) },
      merged_records: processedRecords.length,
      estimates: estimates,
      raw_merged_data: processedRecords
    });

  } catch (error) {
    console.error('❌ 测试预估值计算失败:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// 生成预估值（清空并基于近一年 pph_records 计算写入），采用正确的计算逻辑
app.post('/api/estimates/generate', async (req, res) => {
  try {
    console.log('🛠️ 开始生成PPH预估值 (使用正确的计算逻辑) ...');
    const result = await generateEstimatesCoreCorrect();
    console.log('✅ 预估值生成完成', result);
    res.json({ success: true, ...result });
  } catch (error) {
    console.error('❌ 生成预估值异常:', error);
    res.status(500).json({ success: false, error: '生成预估值异常: ' + error.message });
  }
});
// 定时：每月月初自动重建预估值
function scheduleMonthlyEstimates() {
  try {
    // 每天凌晨 02:00 触发一次检查：如果是每月1号则执行生成
    const msInDay = 24 * 60 * 60 * 1000;

    async function runIfFirstDay() {
      try {
        const now = new Date();
        if (now.getDate() === 1) {
          console.log('⏰ 每月1号定时任务触发：开始自动生成预估值');
          const result = await generateEstimatesCoreCorrect();
          console.log('✅ 每月自动生成预估值完成:', result);
        } else {
          console.log('⏰ 定时检查：今日非每月1号，跳过自动生成');
        }
      } catch (err) {
        console.error('❌ 每月预估值自动生成失败:', err.message || String(err));
      }
    }

    // 计算距离下一个 02:00 的毫秒数
    function msUntilNext2AM() {
      const now = new Date();
      const next = new Date(now);
      next.setHours(2, 0, 0, 0);
      if (next <= now) {
        next.setDate(next.getDate() + 1);
      }
      return next - now;
    }

    setTimeout(() => {
      runIfFirstDay();
      setInterval(runIfFirstDay, msInDay);
    }, msUntilNext2AM());

    console.log('🗓️ 每月预估值定时任务已初始化：每日02:00检查，逢每月1号执行');
  } catch (e) {
    console.warn('⚠️ 定时任务设置异常:', e.message || String(e));
  }
}


// 删除预估值
app.delete('/api/estimates/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await dbPool.query('DELETE FROM pph_estimates WHERE id = ?', [id]);

    if (result && result.changes > 0) {
      return res.json({ message: '删除成功' });
    }
    return res.status(404).json({ error: '预估值记录不存在' });
  } catch (error) {
    console.error('删除预估值失败:', error);
    res.status(500).json({ error: '删除预估值失败', details: error.message });
  }
});


// Excel导出API - 预估值
app.post('/api/excel/export/estimates', (req, res) => {
  try {
    const { estimates } = req.body;

    if (!estimates || !Array.isArray(estimates)) {
      res.status(400).json({ error: '无效的数据格式' });
      return;
    }

    console.log(`📊 开始导出预估值数据，共 ${estimates.length} 条记录`);

    // 创建工作簿
    const workbook = XLSX.utils.book_new();

    // 字段映射配置
    const fieldMapping = {
      material_code: '物料编码',
      product_name: '产品名称',
      line_name: '生产线',
      estimate_pph: '预估PPH值',
      created_at: '创建时间',
      updated_at: '更新时间',
      created_by: '创建人'
    };

    // 格式化日期函数
    function formatDate(dateValue) {
      if (!dateValue) return '-';
      try {
        const date = new Date(dateValue);
        if (isNaN(date.getTime())) return dateValue;
        return date.toISOString().split('T')[0]; // YYYY-MM-DD格式
      } catch (error) {
        return dateValue;
      }
    }

    // 准备数据 - 完整字段映射和格式化
    const exportData = estimates.map(estimate => ({
      [fieldMapping.material_code]: estimate.material_code || '-',
      [fieldMapping.product_name]: estimate.product_name || '-',
      [fieldMapping.line_name]: estimate.line_name || '-',
      [fieldMapping.estimate_pph]: estimate.estimate_pph ? parseFloat(estimate.estimate_pph).toFixed(2) : '-',
      [fieldMapping.created_at]: formatDate(estimate.created_at),
      [fieldMapping.updated_at]: formatDate(estimate.updated_at),
      [fieldMapping.created_by]: estimate.created_by || '-'
    }));

    // 创建工作表
    const worksheet = XLSX.utils.json_to_sheet(exportData);

    // 优化列宽设置
    const colWidths = [
      { wch: 18 }, // 物料编码
      { wch: 35 }, // 产品名称
      { wch: 15 }, // 生产线
      { wch: 15 }, // 预估PPH值
      { wch: 15 }, // 创建时间
      { wch: 15 }, // 更新时间
      { wch: 12 }  // 创建人
    ];
    worksheet['!cols'] = colWidths;

    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH预估值数据');

    // 生成文件名
    const filename = `PPH预估值数据_${new Date().toISOString().split('T')[0]}.xlsx`;

    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
    res.setHeader('Cache-Control', 'no-cache');

    // 生成Excel文件并发送
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    res.end(buffer);

  } catch (error) {
    console.error('Excel导出失败:', error);
    res.status(500).json({ error: '导出失败: ' + error.message });
  }
});

// Excel导出API - 目标值
app.post('/api/excel/export/targets', (req, res) => {
  try {
    const { targets } = req.body;

    if (!targets || !Array.isArray(targets)) {
      res.status(400).json({ error: '无效的数据格式' });
      return;
    }

    console.log(`📊 开始导出目标值数据，共 ${targets.length} 条记录`);

    // 创建工作簿
    const workbook = XLSX.utils.book_new();

    // 字段映射配置
    const fieldMapping = {
      material_code: '物料编码',
      product_name: '产品名称',
      product_series: '产品系列',
      line_name: '生产线',
      record_date: '生效日期',
      takt_time: '标准节拍(秒)',
      whole_machine_target: '整机目标PPH',
      motor_target: '电机目标PPH',
      standard_staff: '标准人数',
      created_at: '创建时间',
      updated_at: '更新时间',
      created_by: '创建人',
      is_active: '状态'
    };

    // 格式化日期函数
    function formatDate(dateValue) {
      if (!dateValue) return '-';
      try {
        const date = new Date(dateValue);
        if (isNaN(date.getTime())) return dateValue;
        return date.toISOString().split('T')[0]; // YYYY-MM-DD格式
      } catch (error) {
        return dateValue;
      }
    }

    // 格式化数值函数
    function formatNumber(value, decimals = 1) {
      if (value === null || value === undefined || value === '') return '-';
      const num = parseFloat(value);
      return isNaN(num) ? '-' : num.toFixed(decimals);
    }

    // 准备数据 - 完整字段映射和格式化
    const exportData = targets.map(target => ({
      [fieldMapping.material_code]: target.material_code || '-',
      [fieldMapping.product_name]: target.product_name || '-',
      [fieldMapping.product_series]: target.product_series || '-',
      [fieldMapping.line_name]: target.line_name || '-',
      [fieldMapping.record_date]: formatDate(target.record_date),
      [fieldMapping.takt_time]: formatNumber(target.takt_time, 2),
      [fieldMapping.whole_machine_target]: formatNumber(target.whole_machine_target, 2),
      [fieldMapping.motor_target]: formatNumber(target.motor_target, 2),
      [fieldMapping.standard_staff]: target.standard_staff ? parseInt(target.standard_staff) : '-',
      [fieldMapping.created_at]: formatDate(target.created_at),
      [fieldMapping.updated_at]: formatDate(target.updated_at),
      [fieldMapping.created_by]: target.created_by || '-',
      [fieldMapping.is_active]: target.is_active ? '启用' : '禁用'
    }));

    // 创建工作表
    const worksheet = XLSX.utils.json_to_sheet(exportData);

    // 优化列宽设置
    const colWidths = [
      { wch: 18 }, // 物料编码
      { wch: 35 }, // 产品名称
      { wch: 15 }, // 产品系列
      { wch: 12 }, // 生产线
      { wch: 15 }, // 生效日期
      { wch: 15 }, // 标准节拍
      { wch: 15 }, // 整机目标PPH
      { wch: 15 }, // 电机目标PPH
      { wch: 12 }, // 标准人数
      { wch: 15 }, // 创建时间
      { wch: 15 }, // 更新时间
      { wch: 12 }, // 创建人
      { wch: 10 }  // 状态
    ];
    worksheet['!cols'] = colWidths;

    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, worksheet, 'PPH目标值数据');

    // 生成文件名
    const filename = `PPH目标值数据_${new Date().toISOString().split('T')[0]}.xlsx`;

    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
    res.setHeader('Cache-Control', 'no-cache');

    // 生成Excel文件并发送
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    res.end(buffer);

  } catch (error) {
    console.error('Excel导出失败:', error);
    res.status(500).json({ error: '导出失败: ' + error.message });
  }
});

// Excel导出API - 异常汇总（周）
app.post('/api/excel/export/abnormal-weekly', (req, res) => {
  try {
    const { data } = req.body;

    if (!data || !Array.isArray(data)) {
      res.status(400).json({ error: '无效的数据格式' });
      return;
    }

    // 创建工作簿
    const workbook = XLSX.utils.book_new();

    // 准备数据 - 将前端传来的数据转换为Excel格式
    const exportData = data.map(row => ({
      '周数': row['周数'] || '',
      '日期': row['日期'] || '',
      '生产线': row['生产线'] || '',
      '异常类别': row['异常类别'] || '',
      '发生次数': row['发生次数'] || 0,
      '关闭次数': row['关闭次数'] || 0,
      '关闭率(%)': row['关闭率(%)'] || '0.0%'
    }));

    // 创建工作表
    const worksheet = XLSX.utils.json_to_sheet(exportData);

    // 设置列宽
    const colWidths = [
      { wch: 10 }, // 周数
      { wch: 12 }, // 日期
      { wch: 10 }, // 生产线
      { wch: 15 }, // 异常类别
      { wch: 10 }, // 发生次数
      { wch: 10 }, // 关闭次数
      { wch: 12 }  // 关闭率(%)
    ];
    worksheet['!cols'] = colWidths;

    // 添加工作表到工作簿
    XLSX.utils.book_append_sheet(workbook, worksheet, '异常汇总（周）');

    // 生成文件名
    const filename = `异常汇总周报表_${new Date().toISOString().split('T')[0]}.xlsx`;

    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
    res.setHeader('Cache-Control', 'no-cache');

    // 生成Excel文件并发送
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    res.end(buffer);

  } catch (error) {
    console.error('异常汇总（周）导出失败:', error);
    res.status(500).json({ error: '导出失败: ' + error.message });
  }
});

// 添加详细的请求日志中间件
app.use((req, res, next) => {
  const timestamp = new Date().toISOString();
  const method = req.method;
  const url = req.url;
  const userAgent = req.get('User-Agent') || 'Unknown';
  const clientIP = req.ip || req.connection.remoteAddress || 'Unknown';
  const origin = req.get('Origin') || 'Unknown';

  console.log(`📥 [${timestamp}] 收到请求:`);
  console.log(`   方法: ${method}`);
  console.log(`   路径: ${url}`);
  console.log(`   客户端IP: ${clientIP}`);
  console.log(`   来源: ${origin}`);
  console.log(`   用户代理: ${userAgent}`);

  // 记录响应
  const originalSend = res.send;
  res.send = function(data) {
    const responseTime = Date.now() - req.startTime;
    console.log(`📤 [${new Date().toISOString()}] 响应发送:`);
    console.log(`   状态码: ${res.statusCode}`);
    console.log(`   响应时间: ${responseTime}ms`);
    console.log(`   响应大小: ${data ? data.length : 0} bytes`);

    return originalSend.call(this, data);
  };

  req.startTime = Date.now();
  next();
});

// 添加错误处理中间件
app.use(enhancedMiddleware.errorHandler());

// 僵尸任务自动清理
async function cleanupZombieJobs() {
  try {
    const zombieThreshold = 4 * 60 * 60 * 1000; // 4小时无更新视为僵尸任务
    const now = new Date();
    const cutoffTime = new Date(now.getTime() - zombieThreshold);

    // 查找可能的僵尸任务（基于started_at时间，因为recompute_jobs表没有updated_at字段）
    const zombieJobs = await dbPool.query(`
      SELECT id, created_at, started_at
      FROM recompute_jobs
      WHERE status = 'running'
      AND datetime(started_at) < datetime(?)
    `, [cutoffTime.toISOString()]);

    if (zombieJobs && zombieJobs.length > 0) {
      console.warn(`🧟 检测到 ${zombieJobs.length} 个可能的僵尸任务，自动标记为失败`);

      // 将僵尸任务标记为失败
      const result = await dbPool.query(`
        UPDATE recompute_jobs
        SET status = 'failed',
            error = '任务超时，自动标记为失败（僵尸任务清理）',
            finished_at = datetime('now')
        WHERE status = 'running'
        AND datetime(started_at) < datetime(?)
      `, [cutoffTime.toISOString()]);

      console.log(`✅ 已自动清理 ${result?.changes || 0} 个僵尸任务`);
    }
  } catch (e) {
    console.error('❌ 僵尸任务清理失败:', e);
  }
}

// 批量更新异常汇总数据API
app.put('/api/records/abnormal-summary/batch', async (req, res) => {
  try {
    const { updates } = req.body;

    if (!Array.isArray(updates) || updates.length === 0) {
      return res.status(400).json({
        success: false,
        error: '更新数据不能为空'
      });
    }

    console.log('🔄 批量更新异常汇总数据:', updates.length, '条记录');

    const results = [];

    for (const update of updates) {
      const { groupKey, newValues } = update;

      if (!groupKey || !newValues) {
        results.push({ success: false, error: '缺少必要的分组键或新值' });
        continue;
      }

      try {
        // 构建更新SQL
        const updateFields = [];
        const updateParams = [];

        if (newValues.abnormality_category !== undefined) {
          updateFields.push('abnormality_category = ?');
          updateParams.push(newValues.abnormality_category === '' ? null : newValues.abnormality_category);
        }

        if (newValues.abnormality_description !== undefined) {
          updateFields.push('abnormality_description = ?');
          updateParams.push(newValues.abnormality_description);
        }

        if (newValues.abnormality_closed !== undefined) {
          updateFields.push('abnormality_closed = ?');
          updateParams.push(newValues.abnormality_closed);
        }

        updateFields.push('updated_at = CURRENT_TIMESTAMP');

        // 构建WHERE条件
        const whereConditions = [];
        const whereParams = [];

        if (groupKey.record_date) {
          whereConditions.push('record_date = ?');
          whereParams.push(groupKey.record_date);
        }

        if (groupKey.line_name) {
          whereConditions.push('line_name = ?');
          whereParams.push(groupKey.line_name);
        }

        if (groupKey.order_number) {
          whereConditions.push('order_number = ?');
          whereParams.push(groupKey.order_number);
        }

        if (groupKey.material_code) {
          whereConditions.push('material_code = ?');
          whereParams.push(groupKey.material_code);
        }

        if (groupKey.product_name) {
          whereConditions.push('product_name = ?');
          whereParams.push(groupKey.product_name);
        }

        if (updateFields.length === 1) { // 只有updated_at
          results.push({ success: false, error: '没有要更新的字段' });
          continue;
        }

        if (whereConditions.length === 0) {
          results.push({ success: false, error: '缺少WHERE条件' });
          continue;
        }

        // 如果指定了原始异常类别，则作为额外的限定条件，避免跨类别误更新
        if (groupKey.abnormality_category !== undefined) {
          whereConditions.push('abnormality_category = ?');
          whereParams.push(groupKey.abnormality_category === '' ? null : groupKey.abnormality_category);
        }

        const sql = `
          UPDATE pph_records
          SET ${updateFields.join(', ')}
          WHERE ${whereConditions.join(' AND ')}
        `;

        const allParams = [...updateParams, ...whereParams];

        console.log('🔍 执行更新SQL:', sql);
        console.log('🔍 参数:', allParams);

        const result = await dbPool.query(sql, allParams);
        const affectedRows = result && result.changes || 0;

        results.push({
          success: true,
          affectedRows,
          groupKey
        });

        console.log(`✅ 更新成功: ${affectedRows} 条记录`);

      } catch (error) {
        console.error('❌ 单条更新失败:', error);
        results.push({
          success: false,
          error: error.message,
          groupKey
        });
      }
    }

    const successCount = results.filter(r => r.success).length;
    const totalAffectedRows = results.reduce((sum, r) => sum + (r.affectedRows || 0), 0);

    console.log(`🎉 批量更新完成: ${successCount}/${updates.length} 成功, 共影响 ${totalAffectedRows} 条记录`);

    res.json({
      success: true,
      message: `批量更新完成: ${successCount}/${updates.length} 成功`,
      results,
      summary: {
        total: updates.length,
        success: successCount,
        failed: updates.length - successCount,
        totalAffectedRows
      }
    });

  } catch (error) {
    console.error('❌ 批量更新异常汇总数据失败:', error);
    res.status(500).json({
      success: false,
      error: error.message || '批量更新失败'
    });
  }
});

// 启动PPH平均值自动重算调度器（每天12:00、19:00）
try {
  const { pphScheduler } = require('./scheduler/pph-scheduler.cjs');
  pphScheduler.start();
  console.log('🕐 PPH平均值自动重算调度器已启动');
} catch (e) {
  console.warn('⚠️ 启动PPH平均值调度器失败（不影响启动）:', e.message || String(e));
}

// 启动服务器 - 监听所有网络接口，允许外网访问
const server = app.listen(PPH_BACKEND_PORT, '0.0.0.0', async () => {
  console.log(`🚀 PPH后端服务已启动`);
  console.log(`📍 监听端口: ${PPH_BACKEND_PORT}`);
  console.log(`🌐 监听地址: 0.0.0.0 (所有网络接口)`);
  console.log(`🔗 前端URL: ${FRONTEND_URL}`);
  console.log(`⏰ 启动时间: ${new Date().toISOString()}`);
  console.log(`🧟 僵尸任务自动清理已启用（每30分钟检查一次）`);
  console.log(`🔧 数据库连接池: 最大${dbPool.options.maxConnections}个连接`);
  console.log(`💊 健康检查: 每${dbHealthService.options.checkInterval}ms`);

  // 显示网络接口信息
  const os = require('os');
  const networkInterfaces = os.networkInterfaces();
  console.log(`🌐 可用网络接口:`);

  Object.keys(networkInterfaces).forEach(interfaceName => {
    const interfaces = networkInterfaces[interfaceName];
    interfaces.forEach(iface => {
      if (iface.family === 'IPv4' && !iface.internal) {
        console.log(`   ${interfaceName}: ${iface.address} (${iface.netmask})`);
        console.log(`      可访问地址: http://${iface.address}:${PPH_BACKEND_PORT}`);
      }
    });
  });

  console.log(`📡 API端点:`);
  console.log(`   健康检查: http://localhost:${PPH_BACKEND_PORT}/api/health`);
  console.log(`   API文档: http://localhost:${PPH_BACKEND_PORT}/api`);
  console.log(`🔒 CORS配置: 允许所有来源`);

  // 启动僵尸任务清理定时器
  setInterval(cleanupZombieJobs, 30 * 60 * 1000); // 每30分钟检查一次
  setTimeout(cleanupZombieJobs, 5000); // 启动5秒后立即检查一次

  // 启动即校验/修复 pph_records 表结构（仅该表）
  try {
    const skip = String(process.env.PPH_SKIP_AUTO_ENSURE || '') === '1';
    const hard = String(process.env.PPH_HARD_REBUILD_ON_START || '') === '1';
    if (!skip) {
      let result;
      if (hard) {
        result = await hardRebuildPPHRecords();
      } else {
        // 现在连接池已统一，可以正常进行自检
        result = await ensureTableConsistencyGently();
      }
      console.log('🧱 pph_records 表结构自检结果:', result);
    } else {
      console.log('⏭️ 已按环境变量跳过 pph_records 表结构自检');
    }
  } catch (e) {
    console.warn('pph_records 表结构自检异常(已忽略):', e && e.message);
  }
});

// 🔒 端口冲突处理 - 严格使用3011端口，不允许回退
server.on('error', (error) => {
  if (error.code === 'EADDRINUSE') {
    console.error(`❌ 端口 ${PPH_BACKEND_PORT} 被占用！`);
    console.error('🔧 请使用以下命令清理端口:');
    console.error(`   node scripts/port-manager.cjs clean ${PPH_BACKEND_PORT}`);
    console.error('🔧 或者使用端口管理器:');
    console.error('   node scripts/port-manager.cjs restart');
    process.exit(1);
  } else {
    console.error('❌ 服务器启动失败:', error);
  }
});

// 启动后初始化每月定时任务
try {
  scheduleMonthlyEstimates();
} catch (e) {
  console.warn('⚠️ 预估值定时任务初始化失败（不影响启动）:', e.message || String(e));
}



// 服务启动兜底检查：如当月未生成过预估值，则立即生成（幂等）
(async function ensureCurrentMonthEstimatesOnStart(){
  try {
    // 确保表结构
    ensurePPHEstimatesSchema();
    // 检查当月是否已有生成（以 updated_at 为准）
    const sql = "SELECT COUNT(1) AS cnt FROM pph_estimates WHERE strftime('%Y-%m', COALESCE(updated_at, datetime('now','localtime'))) = strftime('%Y-%m', datetime('now','localtime'))";
    const rows = await dbPool.query(sql, []);
    const cnt = Number(rows?.[0]?.cnt || 0);
    if (cnt > 0) {
      console.log(`🟢 本月已存在预估值数据：${cnt} 条，跳过兜底生成`);
      return;
    }
    console.log('🟡 服务启动兜底：本月尚未生成预估值，开始自动计算...');
    const result = await generateEstimatesCoreCorrect();
    console.log('✅ 兜底生成预估值完成：', result);
  } catch (e) {
    console.error('❌ 预估值兜底检查/生成失败：', e && (e.message || String(e)));
  }
})();

// 设置优雅关闭处理
function setupGracefulShutdown(serverInstance) {
  process.removeAllListeners('SIGTERM');
  process.removeAllListeners('SIGINT');

  process.on('SIGTERM', () => gracefulShutdown('SIGTERM', serverInstance));
  process.on('SIGINT', () => gracefulShutdown('SIGINT', serverInstance));
}

// 初始设置
setupGracefulShutdown(server);

async function gracefulShutdown(signal, serverInstance = server) {
  console.log(`\n🛑 收到${signal}信号，开始优雅关闭...`);

  // 停止接受新连接
  serverInstance.close(() => {
    console.log('✅ HTTP服务器已关闭');
  });

  try {
    // 停止健康检查服务
    dbHealthService.stop();
    console.log('✅ 数据库健康检查服务已停止');

    // 关闭数据库连接池
    await dbPool.close();
    console.log('✅ 数据库连接池已关闭');

    // 关闭数据库连接（无单独db实例，直接退出）
    console.log('🎉 服务器已优雅关闭');
    process.exit(0);
  } catch (error) {
    console.error('❌ 关闭过程中发生错误:', error);
    process.exit(1);
  }
}

// calculateAveragePPH 函数已移除

// PPH调度器状态API
app.get('/api/pph/scheduler/status', async (req, res) => {
  try {
    const { pphScheduler } = require('./scheduler/pph-scheduler.cjs');
    const status = pphScheduler.getStatus();
    res.json({ success: true, status });
  } catch (error) {
    console.error('获取调度器状态失败:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// 重复路由注册已移除，统一使用前面的正式路由

// 🗄️ 初始化数据库备份系统
const DatabaseBackup = require('./scheduler/database-backup.cjs');
const databaseBackup = new DatabaseBackup();

// 启动定时备份
databaseBackup.startScheduledBackup();

// 数据库备份API
app.get('/api/database/backup/status', (req, res) => {
  try {
    const status = databaseBackup.getStatus();
    res.json({ success: true, status });
  } catch (error) {
    console.error('获取备份状态失败:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

app.post('/api/database/backup/create', async (req, res) => {
  try {
    const result = await databaseBackup.createBackup();
    if (result) {
      res.json({ success: true, message: '数据库备份创建成功' });
    } else {
      res.status(500).json({ success: false, error: '备份创建失败' });
    }
  } catch (error) {
    console.error('创建备份失败:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

app.get('/api/database/backup/list', (req, res) => {
  try {
    const backups = databaseBackup.listBackups();
    res.json({ success: true, backups });
  } catch (error) {
    console.error('列出备份失败:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

console.log('✅ 数据库自动备份系统已启动');

